cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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:

And this is the expected result:

Any help would be much appreciated.

Thanks,

Brendan

1 ACCEPTED SOLUTION
Memorable Member

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!

6 REPLIES 6
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))
)

Regards,

Harsh Nathani

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

Memorable Member

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!

Helper III

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

Memorable Member

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!

Helper I

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

Helper III

Hi Steve,

Many thanks.  That's worked exactly as expected.

Brendan

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.