Forum Discussion

Cliff09's avatar
Cliff09
Regular Visitor
8 years ago
Solved

12 Month Rolling average.

Hi,

 

I have been asked to show our monthly sales with a moving average in a table (and chart). Here is some sample data:

 

Sale DateSale Amount
2/07/2017$15,000
11/07/2017$20,000
19/07/2017$4,000
21/07/2017$4,000
21/07/2017$60,000
2/08/2017$21,000
3/08/2017$15,000
19/08/2017$5,500
19/08/2017$15,000
29/08/2017$20,000
29/08/2017$4,000
1/09/2017$10,000
15/09/2017$15,000
17/09/2017$5,500
18/09/2017$15,000
25/09/2017$20,000
2/10/2017$4,000
5/10/2017$60,000
12/10/2017$21,000
26/10/2017$8,000
28/10/2017$11,000
28/10/2017$500
28/10/2017$15,000

 

It is simple to display the monthly totals, but I am stuck getting the rolling average to work. I have tried messing with a number of DAX expressions that solved similar problem on the forums but can't find exactly what I need.

 

This is the result I am after:

 

Sale MonthTotal Monthly Sales12 Month Moving Average 
Jul-17$103,000 
Aug-17$80,500 
Sep-17$65,500 
Oct-17$119,500 

 

 

Thanks,

Cliff

  • SivaMani's avatar
    SivaMani
    8 years ago

    Cliff09,

     

    Apologise for the late response. Addressed your issue.

     

    Here is the solution,

     

    Step 1: Create a quick measure -> Rolling average

     

     

    Step 2: Edit the DAX of the quick measure -> Change AVERAGEX to SUMX

     

    That's it

16 Replies

  • SivaMani's avatar
    SivaMani
    Resident Rockstar
    8 years ago

    Cliff09,

     

    Try this and let me if it's working or not,

     

    Sales Value L12M =

    VAR  __EndDate = EOMONTH(LASTDATE(‘Sales’[Date]),0)

    VAR  __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate) - 12,1)

    RETURN

    CALCULATE(SUM(‘Sales’[Value]), DATESBETWEEN(‘Sales’ [Date], __StartDate, __EndDate))

    • Cliff09's avatar
      Cliff09
      Regular Visitor
      8 years ago

      Hi SivaMani,

       

      That is just repeating the value from the 'Total Monthly Sales' Column.

      • SivaMani's avatar
        SivaMani
        Resident Rockstar
        8 years ago

        If you create this as a calculated column, it repeats the value.

         

        Please, create as a calculated measure.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hi, i have been using quick measures to calculate rolling average of the last 3,6,12 and 18 months to generate the rankings at work, for some odd reason the averages dont work anymore since we are in a new year, can someone help i am stuck

    • SivaMani's avatar
      SivaMani
      Resident Rockstar
      7 years ago

      Can you share more detail about your issue with sample data?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi SivaMani

         

        i have sent you a link to my pbix file via private message

         

        kind regards

        HiltonM

  • jmf2244's avatar
    jmf2244
    New Member
    7 years ago

    I do the rolling average and the result is ok. But when I graph the measure in a timeline, it shows datapoints into the future. E.g., let's say my data ends on 4/1/19 and I calculate a 10-day rolling average. The timeline goes up to 4/11/19, i.e. 10 days after my last data point. How can I make my graph end on 04/01/2019?

     

    I would really appreciate help on this!

     

  • jmf2244's avatar
    jmf2244
    New Member
    7 years ago

    I do the rolling average and the result is ok. But when I graph the measure in a timeline, it shows datapoints into the future. E.g., let's say my data ends on 4/1/19 and I calculate a 10-day rolling average. The timeline goes up to 4/11/19, i.e. 10 days after my last data point. How can I make my graph end on 04/01/2019?

     

    I would really appreciate help on this!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi jmf2244 

       

      This is 10 days rolling average I used on one of my reports and it works well according to your issue.

      kindly find the example attached and please let me know if it works:

       

      Revenue rolling average =
      CALCULATE(IF([Revenue]<>BLANK();
          
          VAR __LAST_DATE = LASTDATE('Date'[Date].[Date])
          RETURN
              AVERAGEX(
                  DATESBETWEEN(
                      'Date'[Date].[Date];
                      DATEADD(__LAST_DATE; -10; DAY);
                      DATEADD(__LAST_DATE; 1; DAY)
                  );
                  CALCULATE([Revenue])
              )
      ))
      • jmf2244's avatar
        jmf2244
        New Member
        7 years ago

        Thanks Anonymous . However, I am getting the following error: "A single value for column 'Revenue' in table 'xxxx' cannot be determined." (I replaced "Revenue" and "xxxx" with the real names to follow your example)

        It seems like IF needs a single value in the logical test, not a table, right?

         

         

  • JLKM's avatar
    JLKM
    Frequent Visitor
    4 years ago

    Hi All,

     

    I don't see 'Rolling average' in Quick measure. Is it removed/deprecated?

     

    --

    Yours