cancel
Showing results for
Did you mean: 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  Community Support

Hi @ub2811 ,

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.  Community Support

Hi @ub2811 ,

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.  Announcements #### Welcome to the User Group Public Preview  