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

Monthly average of previous 5 years

Hi everyone, 

 

I have consumption data for many buildings from Jan 2017 to July 2023. I want to get the values for FY24 electric consmption (kWh) by averaging the values of the same month during the previous 5 years. For example July 2023 values in Excel would be = Average(July2022, July2021, July2020, July2019, July2018).
How can I calculate this in Power BI to get the values for July, August, Sept etc.. ?


This image shows the data where I have consumption_cost table and it is related to the calendar table. 

abazzan_1-1695315243542.png

 

This is what I tried and I know its wrong but I am still not quite good in Dax to get this. The filter for Fiscal Year is used in this second image along with other filters.

abazzan_2-1695315502713.png

 

I could really appreciate some assistance here and let me know if you need more explanation or data. Thank you very much.

 

@amitchandak @parry2k @lbendlin 

 

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@abazzan ok try this, for testing purpose add a new column as described and then use that in the measure:

 

New MMMM-YYYY Column = FORMAT( 'Calendar'[MMMM-YYYY], "MMMM-YYYY" )

Average Last 5 Years = 
VAR __NumberOfYears = 5
VAR __CurrentMonth = MAX ( 'Calendar'[Date] )
VAR __LastNMonths = 
SELECTCOLUMNS (
    GENERATE ( 
        GENERATESERIES ( 0, __NumberOfYears - 1, 1 ), 
        SELECTCOLUMNS ( { EOMONTH ( __CurrentMonth, -[Value] * 12 ) }, "@Month", [Value] ) 
    ), 
    "@Month", FORMAT ( [@Month], "MMMM YYYY" ) 
)
VAR __LastNMonthsDates = 
    CALCULATETABLE ( 
        VALUES ( 'Calendar'[Date] ), 
        ALL ( 'Calendar' ), 
        TREATAS ( __LastNMonths, 'Calendar'[New MMMM-YYYY Column] ) 
    )
VAR __TotalConsumptionInMonths = 
CALCULATE ( 
    [Sum Consumption],
    __LastNMonthsDates
)
VAR __AverageConsumption = DIVIDE ( __TotalConsumptionInMonths, __NumberOfYears )
RETURN
__AverageConsumption 


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.

View solution in original post

parry2k
Super User
Super User

@abazzan here is improved DAX measure:

 

Average kWh Last 5 Years (Predicted Value) = 
VAR __NumberOfYears = 5
VAR __CurrentMonth = MAX ( 'Calendar'[Date] )
//use following if you need to exclude data of current year
//CALCULATE ( MAX ( 'Calendar'[Date] ), DATEADD ( 'Calendar'[Date], -1, YEAR ) )
VAR __LastNMonthsDates = 
 CALCULATETABLE ( 
    VALUES ( 'Calendar'[Date] ), 
    DATESINPERIOD ( 'Calendar'[Date], __CurrentMonth, -__NumberOfYears, YEAR ), 
    'Calendar'[Month Number] = MONTH ( __CurrentMonth ) 
)
VAR __TotalConsumptionInMonths = 
CALCULATE ( 
    [Sum Consumption],
    __LastNMonthsDates
)
VAR __AverageConsumption = DIVIDE ( __TotalConsumptionInMonths, __NumberOfYears )
RETURN
__AverageConsumption 


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.

View solution in original post

22 REPLIES 22
parry2k
Super User
Super User

@CoreyP thanks for sharing, it would do the job but not to my liking, and here is the reason. 

 

- what if we want to make it dynamic, not always 5 years but give the option to users to pick how many years?

- what if the average is not just straight divided by 5 (# of years) but based on how many years data is available? Maybe some items don't have 5 years sales then average will be wrong (in my opinion)

 

Anyhow, if the ask is very basic, your solution will absolutely work but unfortunately, I don't go the easy route. You can blame on me. Well done!



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.

Ahhh, I see. Makes sense. Thank you for the review! Much appreciated

CoreyP
Solution Sage
Solution Sage

@abazzan , is this the same as this post from a few hours ago? https://community.fabric.microsoft.com/t5/Desktop/Averaging-previous-5-year-values-of-same-month/m-p... 

 

@parry2k  I found this interesting and took a stab at it. Curious what you think about what I came up with in the above link? Am I in the ballpark?

parry2k
Super User
Super User

@abazzan here is improved DAX measure:

 

Average kWh Last 5 Years (Predicted Value) = 
VAR __NumberOfYears = 5
VAR __CurrentMonth = MAX ( 'Calendar'[Date] )
//use following if you need to exclude data of current year
//CALCULATE ( MAX ( 'Calendar'[Date] ), DATEADD ( 'Calendar'[Date], -1, YEAR ) )
VAR __LastNMonthsDates = 
 CALCULATETABLE ( 
    VALUES ( 'Calendar'[Date] ), 
    DATESINPERIOD ( 'Calendar'[Date], __CurrentMonth, -__NumberOfYears, YEAR ), 
    'Calendar'[Month Number] = MONTH ( __CurrentMonth ) 
)
VAR __TotalConsumptionInMonths = 
CALCULATE ( 
    [Sum Consumption],
    __LastNMonthsDates
)
VAR __AverageConsumption = DIVIDE ( __TotalConsumptionInMonths, __NumberOfYears )
RETURN
__AverageConsumption 


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.

This is great @parry2k

This is exactly what I needed. 

Again, your assistance is much appreciated!

parry2k
Super User
Super User

@abazzan I looked at the file and it is giving the correct result using 5 years.



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.

Were you able to take a look at the Excel file that I sent ?

I sent an excel file showing the calculation of average of last 5 years.

parry2k
Super User
Super User

@abazzan I think you want to be for the last 5 years, whereas in the screenshot you have data for 6 years and taking the average of 6 years and that's why the numbers are not matching. You need to be clear on your requirements and explain how you want to calculate the average.



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.

No, I sent the wrong screenshot by mistake. It is the same issue, the numbers are different even when we take the 5-year average.

 

I sent you a sample file if you'd like to check it. THanks!

 

parry2k
Super User
Super User

@abazzan very hard to tell why it is not working. I would recommend sharing a sample pbix file to further look into this.



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.

Ok, I will send a sample pbix file to your email. Thanks!

parry2k
Super User
Super User

@abazzan ok try this, for testing purpose add a new column as described and then use that in the measure:

 

New MMMM-YYYY Column = FORMAT( 'Calendar'[MMMM-YYYY], "MMMM-YYYY" )

Average Last 5 Years = 
VAR __NumberOfYears = 5
VAR __CurrentMonth = MAX ( 'Calendar'[Date] )
VAR __LastNMonths = 
SELECTCOLUMNS (
    GENERATE ( 
        GENERATESERIES ( 0, __NumberOfYears - 1, 1 ), 
        SELECTCOLUMNS ( { EOMONTH ( __CurrentMonth, -[Value] * 12 ) }, "@Month", [Value] ) 
    ), 
    "@Month", FORMAT ( [@Month], "MMMM YYYY" ) 
)
VAR __LastNMonthsDates = 
    CALCULATETABLE ( 
        VALUES ( 'Calendar'[Date] ), 
        ALL ( 'Calendar' ), 
        TREATAS ( __LastNMonths, 'Calendar'[New MMMM-YYYY Column] ) 
    )
VAR __TotalConsumptionInMonths = 
CALCULATE ( 
    [Sum Consumption],
    __LastNMonthsDates
)
VAR __AverageConsumption = DIVIDE ( __TotalConsumptionInMonths, __NumberOfYears )
RETURN
__AverageConsumption 


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.

Thanks again for assisting with this @parry2k

 

After trying this, this is the result for the new measure. However, the calculation for the monthly average based on the previous 5 years is wrong. 

abazzan_0-1695660636428.png

These should be the correct values if calculated in Excel. 

abazzan_1-1695660727701.png

 

Do you have an idea of what went wrong ?

parry2k
Super User
Super User

@abazzan so it is of type of date and you change the format to display differently. What is the date value of this column in the date format?



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.

The MMMM YYYY values in the date format show like this for FY24 as an example.

abazzan_0-1695323428765.png

 

Whereas the Date Column always ends at the 28th day of each month. 

 

parry2k
Super User
Super User

@abazzan what is the data type of  MMMM YYYY column? Seems like it is a calculated column. Can you share the expression of this column?



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.

Yes, it is a calculated column with data type date. 

 

MMMM-YYYY =
CALCULATE (
    MAX ( 'calendar'[Date] ),
    FILTER (
        ALL ( 'calendar' ),
        'calendar'[Year] = EARLIER ( 'calendar'[Year] )
            && 'Calendar'[Month Number] = EARLIER ( 'Calendar'[Month Number])
            && WEEKDAY ( 'calendar'[Date], 2 ) = 5
    )
)
parry2k
Super User
Super User

@abazzan MMMM YYYY you have in your visual, is it from date table?

 

Can you just use this MMMM YYYY column and the new measure to see if it works?



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.

It doesn't work with MMMM YYYY as shown in this image. Yes that column is from the date table. 

abazzan_0-1695322306328.png

 

I also tried using the new measure alone in a card visual and it doesn't show data. 
Can you explain what is the [Value] calculated in the SelectColumns var so that I could try to fix it. 

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.

Top Solution Authors