Running Weekly Average

JPScotland — Sun, 28 Feb 2021 12:10:57 GMT

I've read some posts but I can't quite find what I need. Basically we carry out several house repairs per day. So I use a simple CALCULATE ( COUNT on the PK: JobNumber to get the number of jobs per day. I then used the following DAX to work out a running daily average: -

// Average No of Repairs = AVERAGEX ( FILTER (ALLSELECTED ('Date'), 'Date'[Date] <= MAX ('Date'[Date]) ), [Number of Repairs] )

Which gives me a table like this: -

Date	Count of JobNumber	Average No of Repairs
18/01/2021	90	90
19/01/2021	73	82
20/01/2021	64	76
21/01/2021	56	71
22/01/2021	62	69
23/01/2021	14	60
24/01/2021	13	53
25/01/2021	83	57
26/01/2021	48	56

I want to produce a line graph that shows me the data on a weekly basis, So I added a calculated column into my date table that works out the week ending date for any given date (Ending Sunday). So when I remove the Date column and add the WeekEnd Date I get this: -

Week Ending Date	Count of JobNumber	Average No of Repairs
24/01/2021	372	53
31/01/2021	373	53
07/02/2021	515	60

So it seems like it is picking up the average figure at the end of each week. What I technically need is a SUM of those 7 day averages, but I can't seem to reference the Average measure when creating a new CALCULATE(SUM measure.

Maybe there is a better way to do the entire thing? I also have month number in my Date table if it helps.

Any help would be appreciated.

Cheers,

Re: Running Weekly Average

Greg_Deckler — Sun, 28 Feb 2021 13:35:15 GMT

JPScotland I wrote a quick measure for this, you can find it here:

Rolling Weeks - Microsoft Power BI Community

Re: Running Weekly Average

JPScotland — Mon, 01 Mar 2021 15:46:53 GMT

Hi Greg,

Firsty, thanks for replying, I appreciate that.

I have set up the the week starting/ending measures and the rolling weeks start/end.

My first question is can this be altered to Monday to Sunday. I changed the weekday to 2 but it didnt seem to work out.

Also, when doing the average calculation will it group it as one figure? I see you graph moves from week to week but what do you have in your axis?

Cheers,

topic Re: Running Weekly Average in DAX Commands and Tips

Running Weekly Average

Re: Running Weekly Average

Re: Running Weekly Average