Monthly average of previous 5 years

[abazzan]

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 

[parry2k · Accepted 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 

[parry2k]

@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!

[CoreyP]

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

[CoreyP]

@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 · Accepted solution]

@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 

[abazzan]

This is great @parry2k

This is exactly what I needed. 

Again, your assistance is much appreciated!

[parry2k]

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

[abazzan]

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

[abazzan]

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

[parry2k]

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

[abazzan]

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]

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

[abazzan]

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

[parry2k · Accepted 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 

[abazzan]

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 ?

[parry2k]

@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?

[abazzan]

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. 

[parry2k]

@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?

[abazzan]

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]

@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?

[abazzan]

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.