How create a loop with dax using one month base value?

[Anonymous]

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 

@johnt75 

Thanks in advance!

[PaulDBrown]

Can you please share some sample data or a link to a sample PBIX file?

[Anonymous]

Hi PaulDBrown,

Here is the link for sample data:-

 https://docs.google.com/spreadsheets/d/1RN4F5tlnkeoNP2-Zda6ZejX92jCRnJw2/edit?usp=sharing&ouid=10015...

https://docs.google.com/spreadsheets/d/1RN4F5tlnkeoNP2-Zda6ZejX92jCRnJw2/edit?usp=sharing&ouid=10015... 

Thankyou!

[PaulDBrown · Accepted solution]

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

[Anonymous]

Hi @PaulDBrown 

Thanks a lot for your solution.

[Anonymous]

Hi PaulDBrown, Please find the above tables for sample data.

Thankyou!

PREVIEW

[Anonymous]

Hi PaulDBrown, Please find the above tables for sample data.

Thankyou!

PREVIEW

[Anonymous]

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

[Anonymous]

@MarcelBeug @Vvelarde