Rolling Average of a measure

[lstonehouse]

Hi there,

I'm looking to create a rolling average of staff hours utilisation. 

In the table I have:
[ID] - ID of staff

[Dates] - Dates

[Utilised Hours] - Booked Utilised Hours for the date

[Total Hours] - Total Booked Hours for the date

I've got a measure  [Utilisation] which is

=SUM([Utilised Hours])/SUM([Total Hours])

which gives a percentage utilisation.

I'm now looking to plot a month rolling average, so the average utilization within the past month for each date.

I've try a number of measures including

= CALCULATE( AVERAGEX("Sheet", [Utilisation]), DATESINPERIOD( LastDate(Sheet[Date]), -1, Month))

the problem is that this returns exactly the same as [Utilisation].

If you could give me a hand that would be amazing! 
Cheers!

[Greg_Deckler]

Take a look here at this article:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Should be able to use the same technique I believe. In this case, your rolling average would be your "super group".

[lstonehouse · Accepted solution]

Thanks, that is a good plan. However the data structure I have a bit more complicated than that and so I'm not sure I'd be able to restructure it this way. I'll keep it in mind next time I am building something.

We fixed our own problem with a measure in the end. We went for...

Rolling Average = CALCULATE(SUMX(Sheet, Sheet[Billable hours]), DATESINPERIOD(Sheet[Date] ,LASTDATE(Sheet[Date] ) , -1, MONTH ))  / CALCULATE( SUMX(Sheet ,Sheet [Hours] ), DATESINPERIOD(Sheet [Date], LASTDATE( SHEET [DateTS] ) , -1, MONTH )) 

so performing the same measure as before, but only over the dates in the rolling period, and it works fine. Hope this helps other people.