cancel
Showing results for
Did you mean:
Frequent Visitor

## Running Weekly Average

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,

JP

1 ACCEPTED SOLUTION
Super User IV

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

Rolling Weeks - Microsoft Power BI Community

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

2 REPLIES 2
Super User IV

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

Rolling Weeks - Microsoft Power BI Community

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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,

JP

Announcements