Rolling 3 Year Average

[rogerdea]

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:

ID	Date	Unit
1	02/03/2010	1
2	05/05/2010	2
3	09/06/2010	1
4	01/06/2011	3
5	02/10/2011	1
6	15/12/2011	4
7	14/10/2011	1
8	30/04/2011	2
9	01/01/2012	1
10	06/07/2012	3
11	03/09/2012	2
12	24/12/2012	8
13	18/05/2012	2
14	19/05/2012	1
15	01/03/2013	1
16	02/03/2013	3
17	08/10/2013	6
18	21/08/2013	2
19	02/02/2014	3
20	14/02/2014	1
21	13/05/2014	1
22	16/09/2015	2
23	17/05/2015	2
24	31/07/2015	1
25	01/09/2016	4
26	03/05/2016	1
27	01/02/2016	2
28	30/10/2016	2
29	24/11/2016	3
30	05/04/2017	6
31	06/05/2017	5
32	01/09/2017	2
33	24/12/2017	10
34	31/12/2017	5
35	06/06/2018	2
36	07/08/2018	4
37	09/09/2018	1
38	01/02/2018	6
39	04/10/2019	15
40	20/01/2019	10
41	14/03/2019	8

Expected Results

Year	Sum of Unit	3 Yr Avg
2010	4	4
2011	11	7.5
2012	17	10.6
2013	12	13.3
2014	5	11.3
2015	5	7.3
2016	12	7.3
2017	28	15
2018	13	17.6
2019	33	24.6

[v-xuding-msft · Accepted solution]

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]

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]

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.

[rogerdea]

@v-xuding-msft 

amazing this works in my main pbx file now.  However one thing which doesnt look right on your attached pbx is the 2019 average is incorrect. All others are correct.  Using the same DAX this does work in my file though.

[v-xuding-msft]

Hi @rogerdea ,

Glad it works fine in your sample.😊

For the wrong result, it might be caused by date. The format of date is mm/dd/yyyy rather than dd/mm/yyyy in my sample. I don't change it to match your sample date completely. I just import it and the Desktop change it from your format to mm/dd/yyyy automatically.

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]

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]

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?

[amitchandak]

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]

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.

[amitchandak]

@rogerdea , Please find the attached solution after signature

Check for Avg Unit

[rogerdea]

Thanks @amitchandak 

That's almost working but not quite.  The avg unit field for years 2010, 2011 & 2012 are correct but 2013 onwards are not.  For 2013 i would expect the 'average unit' result to be 13.3 (and not 10.00).  This should be the average of values 11, 17 and 12.

[rogerdea]

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