Forum Discussion

ub2811's avatar
ub2811
Frequent Visitor
5 years ago
Solved

5 YEARS AVERAGE

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!


 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.