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.

     

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.