Running Weekly Average

[JPScotland]

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

[Greg_Deckler · Accepted solution]

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

Rolling Weeks - Microsoft Power BI Community

[JPScotland]

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