Forum Discussion

aortuno's avatar
aortuno
Frequent Visitor
8 years ago

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 ho...
Need Help
aortuno's avatar
aortuno
Frequent Visitor
8 years ago

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

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

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.

  • aortuno's avatar
    aortuno
    Frequent Visitor
    8 years ago

    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 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      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.

      • aortuno's avatar
        aortuno
        Frequent Visitor
        8 years ago

        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