cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

13 Week Rolling Average Graph with Daily %

I know this has been somewhat asked but what I'm trying to do has a slightly different presentaion and I'm having a hard time figuring out how to create a rolling average.

 

Below is a sample of how my data is presented.

 

      DATE       THING     VALUE

1/1/2018A1
1/1/2018B7
1/1/2018C3
1/2/2018A4
1/2/2018B4
1/2/2018C2
1/3/2018A1
1/3/2018B8
1/3/2018C2
1/4/2018A3
1/4/2018B10
1/4/2018C10
1/5/2018A2
1/5/2018B3
1/5/2018C7

 

What I'm trying to do is, have another column to the right of VALUE that shows the average VALUE for each thing over the last number of days (15 or 30 for example).

 

Thanks in advance for the help

10 REPLIES 10
Highlighted
Frequent Visitor

Re: 13 Week Rolling Average Graph with Daily %

Just noticed the table was formatted slightly off. Here you can see more clearly.

 

Table.PNG

Highlighted
Super User IX
Super User IX

Re: 13 Week Rolling Average Graph with Daily %

Have you looked at my rolling weeks Quick Measure?

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694


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

@ 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!




Highlighted
Frequent Visitor

Re: 13 Week Rolling Average Graph with Daily %

No I hadn't seen it. I'm not well versed in DAX and don't know how to extract knowledge from there.


The two key differences that I see comparing it with my data are:

 

  • I have VALUES that I'd like to average
  • I have different THINGS for which I want to find the average separately. 

Nevertheless thank you for your response.

 

Highlighted
Super User V
Super User V

Re: 13 Week Rolling Average Graph with Daily %

Hi,

 

Try this

 

  1. Create a Calendar Table by writing the following formula in Modelling > New Table =CALENDAR(MIN(Data[Date]),MAX(Data[Date]))
  2. Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table
  3. Drag Date from the Calendar Table in your visual
  4. Drag Thing from the Data Table into the visual
  5. Write these measure

 

Total = SUM(Data[Value])

Average over the past 30 days = CALCULATE([Total],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-30,MIN(Calendar[Date])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: 13 Week Rolling Average Graph with Daily %

Hey thanks for you answer, I tried that DAX function and adjusted slightly because it would give me an error

 

 

Average over the past 30 days = CALCULATE(Total = SUM(Data[Value]),DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-30,MIN(Calendar[Date])))

 

It worked but the numbers that it gave me are  much lower than what the averages actually should be. 


Thanks again 

Highlighted
Super User V
Super User V

Re: 13 Week Rolling Average Graph with Daily %

Hi,

 

I will need to see your file to know the error you are committing.  Also, please meniton there what the correct answer should be.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: 13 Week Rolling Average Graph with Daily %

How can I send the file here? 


Below is the table and the column on the right shows the values that I'd like to get automatically.

 

Please note that for this example, the rolling average is calculated based on the last 3 days (not 30), that's why it starts showing on the fourth day. (This is manually calculated)

 

The DAX code that I've used is this:

 

AVERAGE_CALCULATED = CALCULATE(SUM(Sheet1[VALUE])=SUM(Sheet1[VALUE]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-3,MIN('Calendar'[Date])))

 And it displays only 1's on every row. 


Thanks again



 

DATETYPEVALUEAVERAGE
4/1/2018A7 
4/1/2018B6 
4/1/2018C3 
4/2/2018A8 
4/2/2018B7 
4/2/2018C9 
4/3/2018A6 
4/3/2018B5 
4/3/2018C5 
4/4/2018A67.0
4/4/2018B86.0
4/4/2018C75.7
4/5/2018A56.7
4/5/2018B46.7
4/5/2018C57.0
4/6/2018A95.7
4/6/2018B85.7
4/6/2018C75.7
4/7/2018A66.7
4/7/2018B36.7
4/7/2018C56.3
Highlighted
Super User V
Super User V

Re: 13 Week Rolling Average Graph with Daily %

Hi,

 

Remove the =SUM(Sheet1[VALUE]) from within the CALCULATE() function.

 

Does that help?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: 13 Week Rolling Average Graph with Daily %

Not really, it now shows the first three values the same as the first three from the VALUE column (7,6,3) and the rest of the column is empty.


Just to confirm the new formula is: 

 

AVERAGE_CALCULATED = CALCULATE(SUM(Sheet1[VALUE]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-3,MIN('Calendar'[Date])))

 

Thanks again

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors