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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors