Averaging previous 5 year values of same month

[abazzan]

Hello Power BI Community, 

I've been struggling to get the monthly average values for FY24 as Predicted values. I want to get the values of months of FY24 starting July 2023 until June 2024 by averaging the values of the previous years for each month. 

For example: I would need to get the:
Value of July 2023 = ( July2022 + July 2021 + July 2020 + July 2019 + July 2018) / 5

Same thing for August, Sept and so on...

I have consumption data from Jan 2017 to July 2023 now.

I have a calendar table and consumption table

I asked for help and tried these two ways and it didn't work. 

Any thoughts on how to fix this ? 

Thank you!!

@Greg_Deckler 

[parry2k]

@abazzan Thanks for the duplicate post but I hate to say that you haven't even replied to my email. What kind of help are you looking for if you cannot reply to the email? I'm sorry, but your communication is very bad, especially when looking for help. Good luck!

[abazzan]

Hi @parry2k , I totally understand you if thats the case on your side. However, I never received an email from you and I also posted two replies on my previous post asking if you were able to look at it again. 

Unfortunately, I have no access to the Junk emails from the mail I emailed you the file from!

Regarding this second post, I had to post it again to see another solution because I was stuck. Your code now works perfectly fine and I marked your reply as a solution! It was just not working until I changed the:

VAR __CurrentMonth = MAX( 'Consumption_Cost'[Date] ) instead of 'Calendar[Date]' because the Calendar[Date] had dates until 2025.

[CoreyP · Accepted solution]

This seems to work for me. Not sure if it's the most elegant way...

Last 5 Avg =

VAR _PY5 = CALCULATE( SUM( 'Average'[Value] ) , PARALLELPERIOD( 'Calendar'[Date] , -5 , YEAR))

VAR _PY4 = CALCULATE( SUM( 'Average'[Value] ) , PARALLELPERIOD( 'Calendar'[Date] , -4 , YEAR))

VAR _PY3 = CALCULATE( SUM( 'Average'[Value] ) , PARALLELPERIOD( 'Calendar'[Date] , -3 , YEAR))

VAR _PY2 = CALCULATE( SUM( 'Average'[Value] ) , PARALLELPERIOD( 'Calendar'[Date] , -2 , YEAR))

VAR _PY1 = CALCULATE( SUM( 'Average'[Value] ) , PARALLELPERIOD( 'Calendar'[Date] , -1 , YEAR))

VAR _5YAVG = DIVIDE( ( _PY5 + _PY4 + _PY3 + _PY2 + _PY1 ) , 5 , BLANK() )

RETURN

_5YAVG

[abazzan]

Thank you very much Corey, I tried it as well using your method and it works!

Sorry for posting twice but the solution from the first post didn't work and I was stuck. Itworked after I already posted this  @CoreyP