cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JPScotland
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: -

 

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

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

Rolling Weeks - Microsoft Power BI Community

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Greg_Deckler
Super User IV
Super User IV

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

Rolling Weeks - Microsoft Power BI Community

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors