Forum Discussion

rogerdea's avatar
rogerdea
Helper 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 the data begins (2010) the average should be 1 year avg, and for the first two years (2010, 2011) it will be a 2 year avg then moving to the required 3 year averages thereafter.

 

I'm connecting my data to a date table made by using the CALENDAR function.

 

Some sample data is below, which includes expected results following that.  Any help appreciated!

 

DATA:

IDDateUnit
102/03/20101
205/05/20102
309/06/20101
401/06/20113
502/10/20111
615/12/20114
714/10/20111
830/04/20112
901/01/20121
1006/07/20123
1103/09/20122
1224/12/20128
1318/05/20122
1419/05/20121
1501/03/20131
1602/03/20133
1708/10/20136
1821/08/20132
1902/02/20143
2014/02/20141
2113/05/20141
2216/09/20152
2317/05/20152
2431/07/20151
2501/09/20164
2603/05/20161
2701/02/20162
2830/10/20162
2924/11/20163
3005/04/20176
3106/05/20175
3201/09/20172
3324/12/201710
3431/12/20175
3506/06/20182
3607/08/20184
3709/09/20181
3801/02/20186
3904/10/201915
4020/01/201910
4114/03/20198

 

Expected Results

YearSum of Unit3 Yr Avg
201044
2011117.5
20121710.6
20131213.3
2014511.3
201557.3
2016127.3
20172815
20181317.6
20193324.6
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.

     

12 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Helper 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
        Helper 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.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    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
      Helper IV
      6 years ago

      Thanks v-xuding-msft 

       

      I'm getting an error half way through the second part you posted: (blurred my table names for privacy)

      Have i done something wrong?

       

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi rogerdea ,

        There is an extra parenthesis in your formula. I point it out with red pen. Please remove it to have a try.

         

        You could download the pbix file from my answer above. It may help you a little.