Forum Discussion
How create a loop with dax using one month base value?
Hi Everyone,
I was hoping someone could help me with a part of the formula to get me to my end goal.
I want to create a matrix, which shows the "Actual Head Count", for the next two years. I have the base data for October month.
and I have joiners and leavers data. Using these data, I created one measure
*Actual HC = TOTALYTD([HC]+[Joiners]-[Leavers],DATEADD('Calendar'[Date],-1,MONTH))
This measure gives me accurate data up to the current year and starting from next year it shows different data. FYI, please find the bellow attachment.
Below data that I have:-(Base data)
| HC | Month-Year |
| 598 | Oct-22 |
*Now, my requirement is, I want to show the Actual HC up to my calendar end date, by using the "Base Data".
amitchandak, Greg_Deckler , PaulDBrown Jihwan_Kim lbendlin
Thanks in advance!
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
8 Replies
Can you please share some sample data or a link to a sample PBIX file?