Forum Discussion
How create a loop with dax using one month base value?
- 3 years ago
Thanks for the sample data. Since I'm not sure what the data type is for your Month_year fields, I've added a new column to each table to return the end of month date to use for the relationship with the Calendar table:
Ok, so one way to go about this is to use the cumulative values to get the actual HC. So basically this means
Actual HC = [cumulative base head count] + [cumulative joiners] - [cumulative leavers]
I'm assuming that the Base Head Count value of 598 in October-22 is the value before the leavers/joiners of the month take place (in other words, the value at the beggining of the month)
Since the Base count is only for the starting month, the "cumulative" value is constant. So:BHC = CALCULATE(SUM('Base Head count'[HC]), ALL(Calander))For the joiners and leavers, you need measures following this pattern:
Joiners HC = CALCULATE ( SUM ( Joiners[Joiners] ), FILTER ( ALL ( Calander ), Calander[Date] <= MAX ( Calander[Date] ) ) )Finally your Actual HC will be:
Actual HC = [BHC] + [Joiners HC] - [Leavers HC]To get:
Sample PBIX attached
Can you please share some sample data or a link to a sample PBIX file?