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

SivaMani's avatar
SivaMani
Icon for Resident Rockstar rankResident 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
      Icon for Resident Rockstar rankResident Rockstar
      8 years ago

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

       

      Please, create as a calculated measure.

      • Cliff09's avatar
        Cliff09
        Regular Visitor
        8 years ago

        Yes, I have created it as a measure.

         

        Here is the result I get on the sample data: