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 Date Sale Amount 2/07/2017 $15,000 11/07/2017 $20,000 ...
  • 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

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