Average of last full three years

[tuulia]

Hi!

Need tips creating a measure "Average of last full three years". When user selects year 2021 measure should return average of years 2018-20.

One way is to create 3 different measures (parallel period -1, -2, -3 years) and make an avg-measure of those 3, but is it possible to combine these in one measure?

And I need to make this avg-measure from calculated measure like (F_Sales[amount],Dim1[code]=3])

[v-jingzhang]

Hi @tuulia 

Can you share whether these replies have solved your problem? If solved, kindly accept the appropriate one as solution. If not, can you describe your problem and expected result with some dummy data? The table structures and relationships will influence how a measure should be created to achieve your desired output. Without data, we can only imagine a table and create a measure based on that. Thanks.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[sanalytics]

@tuulia 

You can also use custom time intelligence for creating this measure..Refer the sceenshot below

Regards

sanalytics

[Jihwan_Kim · Accepted solution]

Hi, @tuulia 

Please correct me if I wrongly understood your question.

Please check the below measure and the link down below whether it is what you are looking for.

Average QTY of Last Full Three Years =
VAR currentyear =
MAX ( Dates[Year] )
RETURN
AVERAGEX (
FILTER (
ALL ( Dates[Year] ),
Dates[Year] >= currentyear - 4
&& Dates[Year] < currentyear
),
[Qty total]
)

https://www.dropbox.com/s/q3broy2yupyt8ay/tuulia.pbix?dl=0 

[tuulia]

Thank you very much, this worked (with little changes)!

[amitchandak]

@tuulia , If you have date, then with help from date table

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,Year))/3

or

Rolling 3 = CALCULATE(AverageX(values('Date'[Year]),calculate(sum(Sales[Sales Amount])),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,Year))

Only year, with separate year table(say Date)

rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-3 && 'Date'[Year]<=max('Date'[Year])) )/3

Or use average function