Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
GJ217
Resolver III
Resolver III

Previous 12 Month Rolling Average Headcount in DAX

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

 

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

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] )

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

3 REPLIES 3
GJ217
Resolver III
Resolver III

@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!

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

 

All the best champion!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

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] )

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.