5 YEARS AVERAGE

[ub2811]

Hello there,

I hope soemone can help.

I have to claulcate a prior 5 years average based on the max date seleted in date slicer i.e

If max date selected in slicer is 05/06/2021 then I need montlhy based average for last 5 year, i.e 2016 to 2020.

I have calculated that already and only works fine when i select datae filter from 2016 to 2021 and it gives me 2016 to 2020 avg.

But if date in slicers are selected from 2020 to 2021. Then it just gives 1 year average.

My axis in visualization is "Month". I can't select "Before" option on slicer.

5Yrs Avg =

var MaxDate = CALCULATE(DATE(YEAR(MAX('Date'[Date]))-1,12,31),ALLSELECTED('Date'))

var Mindate = DATE(YEAR(MaxDate)-4,1,1)

Return

CALCULATE(divide([sum],count('Date'[Date]) ,FILTER('Date','Date'[Date]>=[Mindate]&&'Date'[Date]<=[MaxDate]),VALUES('Date'[Unique Month Number]))

The reason I haven't uses Averagex formula because some of dates don;t have any data and I need divide sum in a month by number of days.

Any help will highly be appreciated!

[v-stephen-msft · Accepted solution]

Hi @ub2811 ,

Sorry for my late reply.

Here's my demo you can check.

1.Create a calendar table.

2.Create a measure.

5 YEAR AVERAGE = 
VAR A1 =
    CALCULATE (
        SUM ( 'Date'[Unique Month Number] ),
        FILTER ( 'Date', YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) - 1 )
    )
VAR A2 =
    CALCULATE (
        SUM ( 'Date'[Unique Month Number] ),
        FILTER ( 'Date', YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) - 2 )
    )
VAR A3 =
    CALCULATE (
        SUM ( 'Date'[Unique Month Number] ),
        FILTER ( 'Date', YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) - 3 )
    )
VAR A4 =
    CALCULATE (
        SUM ( 'Date'[Unique Month Number] ),
        FILTER ( 'Date', YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) - 4 )
    )
VAR A5 =
    CALCULATE (
        SUM ( 'Date'[Unique Month Number] ),
        FILTER ( 'Date', YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Year] ) - 5 )
    )
VAR BB =
    CALCULATE (
        MAX ( 'Calendar'[Days] ),
        FILTER (
            'Calendar',
            YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Year] )
                && [Month] = MONTH ( MAX ( 'Date'[Date] ) )
        )
    )
RETURN
    DIVIDE (
        DIVIDE ( A1, BB ) + DIVIDE ( A2, BB )
            + DIVIDE ( A3, BB )
            + DIVIDE ( A4, BB )
            + DIVIDE ( A5, BB ),
        5
    )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.