Calculate Average of current and previous two years

[tonyclifton]

Hello community,

I have the following (simplified) data. I want to calculate the average of three years based on below table and show that value for the according year:

Year	YearQuarter	Value
2013	2013-01	20
2013	2013-02	40
2013	2013-03	35
2013	2013-04	10
2014	2014-01	44
2014	2014-02	16
2014	2014-03	15
2014	2014-04	10
2015	2015-01	61
2015	2015-02	35
2015	2015-03	22
2015	2015-04	20

And this would be the expected output for 2015:

Year	Value	Average of 3 years
2013	105	…
2014	85	…
2015	138	109

109 = (105 + 85 + 138) / 3

I hope you have an idea here because I am stuck.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi, @tonyclifton 

Please check the below picture and the sample pbix file's link down below.

Value Total Measure =

SUM('Table'[Value])

Avg of 3 years =

VAR currentyear =

MAX ( 'Table'[Year] )

RETURN

IF (

currentyear - 1

< MINX ( ALL ( 'Table'[Year] ), 'Table'[Year] )

|| currentyear - 2

< MINX ( ALL ( 'Table'[Year] ), 'Table'[Year] ),

BLANK (),

AVERAGEX (

CALCULATETABLE (

VALUES ( 'Table'[Year] ),

FILTER (

ALL ( 'Table' ),

'Table'[Year] >= currentyear - 2

&& 'Table'[Year] <= currentyear

)

),

[Value Total Measure]

)

)

https://www.dropbox.com/s/vxbgr8s1rvemaah/tony.pbix?dl=0 

[tonyclifton]

Thank you very much @Jihwan_Kim . This works perfectly.

[selimovd]

Hey @tonyclifton ,

depending if you want to calculate whole years or from the max date or filter date you can use different approaches.

Check the function DATESINPERIOD function (DAX) - DAX | Microsoft Docs or PARALLELPERIOD function (DAX) - DAX | Microsoft Docs that can both extend the filter context of your date to 3 years.

Also be aware you need a proper date table in order to make the time intelligence functions work:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic