cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Averaging previous 5 year values of same month

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!!

1 ACCEPTED SOLUTION
Solution Sage

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

4 REPLIES 4
Super User

@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!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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.

Solution Sage

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

Helper I

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.