Previous 12 Month Rolling Average Headcount in DAX

[GJ217]

Hi All,

I really need some help with this one. I have the below Avg. Headcount formula which calculates Headcount based on the last 12 months with a Relative Date filter on the page in the last 12 Months which as currently written shows 01/11/23 - 31/10/23.

Avg. Headcount = 
VAR v_dates = DATESINPERIOD(DimDates[Date], MAX(DimDates[Date]), -12, MONTH)
RETURN
AVERAGEX(v_dates, [Headcount])

This is the Headcount measure called out in the Avg. Headcount measure above.

Headcount = CALCULATE(DISTINCTCOUNT(PersonDetails[PERSON NUMBER]),
FILTER(VALUES(PersonDetails[WORK RELATIONSHIP START DATE]), PersonDetails[WORK RELATIONSHIP START DATE] <=MAX(DimDates[Date])),
FILTER(VALUES(PersonDetails[TERMINATION DATE]), OR(PersonDetails[TERMINATION DATE] >=MAX(DimDates[Date]),ISBLANK(PersonDetails[TERMINATION DATE]))))+0

I have have been asked to provide the previous rolling 12 months figures (01/10/22 - 30/09/23) but this can only be done using DAX without the Relative Date filter and in order for me to calcuate the the turnover in the previous 12 month period upto and including the last working date of the previous month, I need to calculate the average headcount for that period.

This is where I'm stuck. I've managed to calculate Leavers without using the Relative Date filter on the page and they match the figures where Realative Date filters on the page have been applied. However I need a formula to do the same to calcuate Average Headcount for the previous rolling 12 month period.

P1 Leavers All Prev Rolling 12 Prev Month = 
CALCULATE(
    COUNTROWS(Leavers),
            FILTER(
                    ALL('Leavers'[TERMINATION DATE]),
                        Leavers[TERMINATION DATE] >= EOMONTH(TODAY(), - 13) +1
                         &&
                        Leavers[TERMINATION DATE] <= EOMONTH( TODAY(), -1)
)
) ////Previous rolling 12 calendar months

[GJ217]

@TheoC 

This is spot on! it matches the card visual with the Relative Date in the last 12 calendar months applied on the Page filter.

Thank you so much!

[TheoC]

@GJ217  so glad it worked! That's great 🙂

All the best champion!

[TheoC · Accepted solution]

Hi @GJ217 

I think it's to do with your start and end dates.  

Can you try something like the following:

Avg Headcount Previous 12 Months = 

VAR EndDate = EOMONTH ( TODAY() , -1 )
VAR StartDate = EOMONTH ( TODAY() , -13 ) + 1
VAR v_dates = DATESBETWEEN ( DimDates[Date] , StartDate , EndDate )

RETURN

AVERAGEX ( v_dates , [Headcount] )