Forum Discussion

JPScotland's avatar
JPScotland
Helper I
5 years ago
Solved

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: -

 

DateCount of JobNumberAverage No of Repairs
18/01/20219090
19/01/20217382
20/01/20216476
21/01/20215671
22/01/20216269
23/01/20211460
24/01/20211353
25/01/20218357
26/01/20214856

 

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 DateCount of JobNumberAverage No of Repairs
24/01/202137253
31/01/202137353
07/02/202151560

 

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

2 Replies

    • JPScotland's avatar
      JPScotland
      Helper I

      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