cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ub2811
Frequent Visitor

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!


 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @ub2811 ,

 

Sorry for my late reply.

Here's my demo you can check.

1.Create a calendar table.

10.png

 

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
    )

12.png

 

 

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.

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @ub2811 ,

 

Sorry for my late reply.

Here's my demo you can check.

1.Create a calendar table.

10.png

 

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
    )

12.png

 

 

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors