Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
abazzan
Helper I
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

abazzan_0-1695835793751.png

 

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

abazzan_1-1695835927662.png

abazzan_2-1695835967216.png

 

Any thoughts on how to fix this ? 


Thank you!!

@Greg_Deckler 

 

1 ACCEPTED SOLUTION
CoreyP
Solution Sage
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

 

View solution in original post

4 REPLIES 4
parry2k
Super User
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.

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
Solution Sage
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

 

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 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.