cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bfaws
Helper III
Helper III

Measure for rolling 12 month average

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:

 

Power BI community (3) 02.06.2020.PNG

 

And this is the expected result:

 

Power BI community (4) 02.06.2020.PNG

 

Any help would be much appreciated.

 

Thanks,

 

Brendan

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

rt.png

Power BI file here.

 

Hope it helps. 

Kind regards,

Steve. 

View solution in original post

5 REPLIES 5
harshnathani
Community Champion
Community Champion

Hi @Bfaws ,

 

 

You can try this measure.

 

12monthsRollingAvg =

var _a = MAX('Table'[Report Date])
var _12months = Minx(DATEADD('Table'[Report Date],-12,MONTH),'Table'[Report Date])
Return

//To get average of all the report Dates
//CALCULATE(Average('Table'[Sum of FTE]), Filter(ALL('Table'),'Table'[Report Date] <=_a && 'Table'[Report Date] > _12months))


//To get Average Dates where previous data of 12 months is available
SWITCH(
TRUE(),
CountROWS(FILTER(ALL('Table'),'Table'[Report Date] <=_a && 'Table'[Report Date] > _12months)) = 12,CALCULATE(Average('Table'[Sum of FTE]), Filter(ALL('Table'),'Table'[Report Date] <=_a && 'Table'[Report Date] > _12months))
)
 
1.jpg
 
 
Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 
stevedep
Memorable Member
Memorable Member

Hi,

 

See;

Rolling12month = CALCULATE(    AVERAGE( 'Table'[Sum of FTE]);    DATESINPERIOD( 'Date'[Date]; MAX( 'Date'[Date] ); -12;  MONTH ))

rolling.png

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.  

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

https://1drv.ms/u/s!AtGISPtZ7ajKgQDjcO06sW8SzOPn?e=wHVvjS 

I see,

Rolling12month = 
var v_dates =  DATESINPERIOD( Rolling_Calendar_Lookup[Date]; MAX( Rolling_Calendar_Lookup[Date] ); -12;  MONTH )
return
AVERAGEX(v_dates;[sum FTE])

rt.png

Power BI file here.

 

Hope it helps. 

Kind regards,

Steve. 

Hi Steve, 

 

Many thanks.  That's worked exactly as expected.

 

Brendan

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors