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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.