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

Need Help

10 Replies

  • aortuno's avatar
    aortuno
    Frequent Visitor
    8 years ago

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

     

      • aortuno's avatar
        aortuno
        Frequent Visitor
        8 years ago

        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.

         

    • 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