Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I`m looking for a measure to calculate average Full time equivalent (FTE) over a 12 month rolling period. I have a table that shows the sum of FTE for each month. What I would like is an additional column which shows the previous 12 month rolling average.
Please see what first table looks like:
And this is the expected result:
Any help would be much appreciated.
Thanks,
Brendan
Solved! Go to Solution.
I see,
Rolling12month =
var v_dates = DATESINPERIOD( Rolling_Calendar_Lookup[Date]; MAX( Rolling_Calendar_Lookup[Date] ); -12; MONTH )
return
AVERAGEX(v_dates;[sum FTE])
Power BI file here.
Hope it helps.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi @Bfaws ,
You can try this measure.
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi,
See;
Rolling12month = CALCULATE( AVERAGE( 'Table'[Sum of FTE]); DATESINPERIOD( 'Date'[Date]; MAX( 'Date'[Date] ); -12; MONTH ))
Power BI file is here.
Some tweaking might be needed because the RT needs to be calculated on an aggregate. Pls share detailed data in case this does not work.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve,
Thanks for the response. I have tried this but dont quite get the result. This is probably my fault though as I think i have over-simplied the information provided. I have set up a pbix file which mirrors the real file in terms of format of data loaded and model structure. Can you please have another look.
Much appreciated.
Brendan
I see,
Rolling12month =
var v_dates = DATESINPERIOD( Rolling_Calendar_Lookup[Date]; MAX( Rolling_Calendar_Lookup[Date] ); -12; MONTH )
return
AVERAGEX(v_dates;[sum FTE])
Power BI file here.
Hope it helps.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Steve,
Hopefully you're still active. How would I write this so that it ignores my Date slicer and continues to average back past the 1st date shown? For example, in OPs example April 2017 is average as just one month, but I would like to average the 12 months prior to April 2017.
Thanks
Hi Steve,
Many thanks. That's worked exactly as expected.
Brendan
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
30 | |
16 | |
14 | |
14 | |
9 |