calculate / filter / value functions combined question

[CelineT]

Hi,

I have a dataset with employee details (start date, end date, salary, beneftis, ...) and need to calculate the costs per month.

These are the tables (simplified, I have about a dozen tables linked to each other):
* data: list of employees and their costs, start date and end date
* date_list: disconnected table of the periods I need to display
* index: connected to the date_list table by year

I've got the following formulas:

Meal Vouchers Cost:=
Calculate( [Meal Vouchers];
FILTER(DATA;
COUNTROWS( FILTER( VALUES(DATE_LIST[Dates]);
DATA[change date adj] <= DATE_LIST[Dates] &&
DATA[exit date adj] >= DATE_LIST[Dates] ))
> 0 )
)

(sorry, I've written it nicer, but can't seem to make is look better readable in the post)

and that works perfectly!

But I would need to multiply the above formula with (1+index)

Anyone an idea how I can make that work?

Thanks!

 

[CelineT]

hmm, maybe I should clarify that just multiplying with the index doensn't work. PP has to iterate this on every row.

I'm now thinking towards changing it to cubeformulas, but have the impression that will be a bit overkill for this. this means I would need to add slicers as to which data they want to recover and as I'm quite new that would take quite some time. as to pivot tables is something I know (as well as the client)

 

[gazpage]

It's a little hard to understand exactly the format of your data is and how index is defined (presumably it's different by date?), and you also didn't mention what your measure [Meal Vouchers] does.

I would suggest you look at creating a new measure [Indexed Meal Vouchers] that is based on an iterator. Say you have [Meal Vouchers] := SUM ( Data[VouchersCost] ), then you would have SUMX ( Data, Data[VoucherCost]*(1+index) ). This way your filtering logic will still work.

 

[CelineT]

It's a little hard to understand exactly the format of your data is and how index is defined (presumably it's different by date?), and you also didn't mention what your measure [Meal Vouchers] does.

I would suggest you look at creating a new measure [Indexed Meal Vouchers] that is based on an iterator. Say you have [Meal Vouchers] := SUM ( Data[VouchersCost] ), then you would have SUMX ( Data, Data[VoucherCost]*(1+index) ). This way your filtering logic will still work.

thanks for your feedback. I'll try to provide more details

DATA:

empl1 - start 1/1/2017 ends 1/5/2017 - belgium
empl2 - start 1/3/17 ends 1/3/12 - france

MEAL VOUCHERS:
Belgium - 5€
France - 0€

=> DATA and MEAL VOUCHERS are connected through the country

DATE_LIST (will be flexibel, depending on budget timing)

1/1/17
1/2/17
1/3/17
1/4/17
1/5/17
...
1/12/18
(format: day-month-year)

INDEX
2017 - 2%
2018 - 0%


DATE_LIST and INDEX are connected to each other with year.

There is no connection between DATE_LIST/INDEX on one side and DATA/MEAL VOUCHERS on the other side

Calculations:

There are actually 3 steps in my calculations.
STEP1:
MEAL_VOUCHER_COST = SUMX(DATA; RELATED(MEAL_VOUCHERS[cost]))

STEP 2:
Meal Vouchers_FILTER:=Calculate( [MEAL_VOUCHER_COST];
FILTER(DATA;
COUNTROWS( FILTER( VALUES(DATE_LIST[Dates]);
DATA[start date] <= DATE_LIST[Dates] &&
DATA[end date] >= DATE_LIST[Dates] ))
> 0)
)

STEP 3:
Meal_TOTAL:=SUMX(VALUES(DATE_LIST[Dates (Month)]); [Meal Vouchers_FILTER])

(this last one is needed to make sure that the total column per year is also correct, otherwise he shows the amount for just one month)

I'm pretty new at disconnected slicers and combination of calculate, filter and values (*wauw powerfull!!*) so I don't yet understand all the details, but it works perfectly. I just would like to add the index, but I don't know in which step to do this. I hope I have made everything clear in this description, if not don't hesitate to let me know.

I'll definitely be eternally gratefull is this manages to success!
(I've got about 20 similar calculations like this)

 

[gazpage]

I'm struggling with how you know what index to apply to what row when there is no connection between the dates table and the data table.

Hmmm. I feel like you need to take a step back. Ignoring what you have so far, what are the filters applied in the final output? For example is it months down the side and schools across the top with a slicer for country.

Then can you write in pseudo code what the calculation would be? I.e.:

For March 16 school A

Sum of cost of meals at school A in March 16 where column X has the number of meals, column Y has the cost per meal and colum Z is the dates.

 

[gazpage]

https://expirebox.com/download/3582d79d95382303b3e0b778bdb66119.html

Total Cost = 
SUMX ( 
    VALUES ( DATE_LIST ),   
    CALCULATE (
        SUM ( MEAL_VOUCHERS[Voucher Cost] ),
        FILTER (
            DATA,
            DATA[Start] <= DATE_LIST[Day] &&
            DATA[End] >= DATE_LIST[Day]
        )
    ) * (1 + RELATED ( INDEX[Index] ) )
)

 

[CelineT]

Wauw, you really made my day! I can't believe it was actually quite obvious I guess I tried to input the index about anywhere except where you put it.

Thank you!!!!

And it's also clearer to read when I start from the values(date_list) as you did than what I wrote first with the countrows > 0.
So: eternally gratefull to take your time for this

Power Pivot really rocks!