Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@amitchandak @parry2k @lbendlin
Solved! Go to Solution.
@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.
@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.
@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
@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?
@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.
@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.
@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!
@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!
@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.
These should be the correct values if calculated in Excel.
Do you have an idea of what went wrong ?
@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.
Whereas the Date Column always ends at the 28th day of each month.
@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.
@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.
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |