Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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] )
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |