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?
- Anonymous3 years agoNot applicable
Base Head count Data:-
Adjusted Discipline Week HC Month-Year Base HC 10/1/2022 598 Oct-22 Joiners data:-
Joiners Year Month-Year 16 2022 22-Oct 38 2022 22-Nov 18 2022 22-Dec 7 2023 23-Jan Leavers data:-
Leavers Year Month-Year 22 2022 22-Oct 31 2022 22-Nov 12 2022 22-Dec 2 2023 23-Jan - Anonymous3 years agoNot applicable
- Anonymous3 years agoNot applicable
- Anonymous3 years agoNot applicable
Hi PaulDBrown,
Here is the link for sample data:-
- PaulDBrown3 years agoCommunity Champion
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
- Anonymous3 years agoNot applicable
Hi PaulDBrown
Thanks a lot for your solution.