Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 hav...
  • PaulDBrown's avatar
    PaulDBrown
    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