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

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
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

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

       

      Does that help?

      • aortuno's avatar
        aortuno
        Frequent Visitor
        8 years ago

        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