Forum Discussion

Bfaws's avatar
Bfaws
Icon for Helper III rankHelper III
6 years ago
Solved

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 ad...
  • stevedep's avatar
    stevedep
    6 years ago

    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. 

stevedep's avatar
stevedep
Icon for Memorable Member rankMemorable Member
6 years ago

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. 

mcash's avatar
mcash
Icon for Helper I rankHelper I
3 years ago

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