Forum Discussion

rogerdea's avatar
rogerdea
Icon for Helper IV rankHelper IV
6 years ago
Solved

Rolling 3 Year Average

Hello   I am trying to figure out how to calculate this from my data and struggling with this.  I need the rolling 3 year average of the sum of units, taking into account that for the first year th...
Need Help
Tutorial Requests
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi rogerdea ,

    Please try like this:

    • Create a calendar table.

     

    Date =
VAR _calendar =
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) )
RETURN
    ADDCOLUMNS ( _calendar, "Year", YEAR ( [Date] ) )

     

    • Create a measure.

     

    Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Unit] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Date'[Year] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, YEAR )
    )
)

     

    For more details, please see the attachment.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Typically this how we do rolling with a date calendar. example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,Year)) 
Or
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Year))  

Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,Year)) 
Or
Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

rogerdea's avatar
rogerdea
Icon for Helper IV rankHelper IV
6 years ago

 

Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,Year))

 

 

amitchandak  I tried the above method and i have no errors, but my output is:

 

Each row has a column with the amoun of units (which are all 1).  Does there need to be a SUM somewhere in the DAX to get the yearly sum?

  • rogerdea's avatar
    rogerdea
    Icon for Helper IV rankHelper IV
    6 years ago

    To be clear its the calendar year average i need, eg 1st Jan to 31 Dec each year, summed and then these averaged.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try like

    rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,Year)) 

Avg rolling = Averagex(summarize(table,Date[year],"_1",[rolling 3 ]),[_1])
    • rogerdea's avatar
      rogerdea
      Icon for Helper IV rankHelper IV
      6 years ago

      I've not been able to get that working, the first part just returns a "1" for each year.   But i changed that to the following which works except for the first two years, where there is not enough years data to divide by 3:

       

      Changed average to a sum, and then divided by 3. 

      Rolling 3 = CALCULATE(SUM('FACT DATA'[data_table]),DATESINPERIOD('Date Table'[Date],ENDOFMONTH('FACT DATA'[Date]),-3,YEAR)) /3

       Problem is the first two years are not the correct average because there is not three years of data.