Reply
GauravGG
Frequent Visitor
Partially syndicated - Outbound

Calculate Rolling 12 Month Average Headcount

Hi Experts, 

My formula for Headcount is working fine for each month as shown below
It's just that I don't understand how to calculate 12 Month Rolling Average of this Headcount shown

GauravGG_0-1702137821149.png

I know many have asked this question before, but all the approaches are doing average of a column from a table
My question is different because I have to do average of a measure over 12 months rolling for a given month

Please advise!

 

1 ACCEPTED SOLUTION
GauravGG
Frequent Visitor

Syndicated - Outbound

I used this article from SQLBI https://www.daxpatterns.com/month-related-calculations/ to solve this issue
Attaching solution just in case it helps anyone

GauravGG_0-1702153010206.png

GauravGG_1-1702153021678.png
Sincere Kudos to @Ahmedx  and SQLBI

 

View solution in original post

7 REPLIES 7
GauravGG
Frequent Visitor

Syndicated - Outbound

I used this article from SQLBI https://www.daxpatterns.com/month-related-calculations/ to solve this issue
Attaching solution just in case it helps anyone

GauravGG_0-1702153010206.png

GauravGG_1-1702153021678.png
Sincere Kudos to @Ahmedx  and SQLBI

 

Ahmedx
Super User
Super User

Syndicated - Outbound

Just implemented it- was reading the same article

GauravGG_0-1702141292648.png

This did not work too, the answer is still the same

GauravGG_1-1702141323964.png

This is because my measure Headcount, being evaluated, in itself is Date Dependent, I feel

GauravGG_2-1702141372144.png

Thanks for sharing your time on this with me, I am trying as well
ChatGPT for sure isn't being helpful- lol

 

GauravGG
Frequent Visitor

Syndicated - Outbound

@Ahmedx This did not work too

GauravGG_0-1702140896733.png

GauravGG_1-1702140929949.png

 

GauravGG
Frequent Visitor

Syndicated - Outbound

@Ahmedx Hi There,
Thanks for helping me
I used this myself

GauravGG_0-1702139145202.png


And also tried your suggestion

GauravGG_1-1702139184455.png
With both of these, my Headcount becomes = Rolling Avg HC of 12 Months

GauravGG_2-1702139235222.png

This is becvause my Headcount formula shared above in question is dynamic and uses the Year and Month in Row to calculate itself dynamically each row

 

Syndicated - Outbound

try adding the function REMOVEFILTERS(Calendar)

Ahmedx
Super User
Super User

Syndicated - Outbound

write the new measure like this

 

 NewMeasure = CALCULATE ( [Headcount],

DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-12,MONTH))

 

 

avatar user

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!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)