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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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)