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

## 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.

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.

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

2 ACCEPTED SOLUTIONS
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.

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.

22 REPLIES 22
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.

Solution Sage

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

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?

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.

Helper I

This is great @parry2k

This is exactly what I needed.

Again, your assistance is much appreciated!

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.

Helper I

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

Helper I

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

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.

Helper I

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!

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.

Helper I

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

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.

Helper I

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.

These should be the correct values if calculated in Excel.

Do you have an idea of what went wrong ?

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.

Helper I

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

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

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.

Helper I

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
)
)
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.

Helper I

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

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.