cancel
Showing results for
Did you mean:
Helper II

## Calculate Average of current and previous two years

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.

1 ACCEPTED SOLUTION
Super User

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]
)
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

3 REPLIES 3
Super User

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]
)
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Helper II

Thank you very much @Jihwan_Kim . This works perfectly.

Super User

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

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors