3 Week Rolling Average By Day of Week

[owdtelly88]

Hi All,

I'm trying to create a rolling average based on total hours worked.

I need to group it by each day of the week, cost code and by worker.

My hours are in a table along with activity date, worker id and cost code. Each line is an entry against a cost code and a worker might have one line per day or several if they've been working on multiple cost codes. 

A worker won't necessarily work every day so some may be blank. 

Thanks in advance for any help. 

Chris 

[v-xulin-mstf · Accepted solution]

Hi @owdtelly88,

Try measure as:

Measure = 
CALCULATE(
    AVERAGEX(FILTER(ALL('Table'),WEEKDAY('Table'[Date])=WEEKDAY(MAX('Table'[Date]))),'Table'[Value]),
    DATESINPERIOD('Table'[Date],MAX('Table'[Date]),21,DAY)
)

Here is the output:

The pbix is attached, please try it.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[amitchandak]

@owdtelly88 , Based on what I understand

averageX(summarize(Table, [activity date], [worker id] , [cost code] , "_1", [Measure]),[_1])

[owdtelly88]

Thanks for your reponse. How would I incorporate the above into a three week rolling average please? Also, just to clarify, when you mention measure, you mean sum of hours?