Add 3 Year average to my report

[davehus]

Hi, I need to be able to get a 3 year average a product and also be able to display it by month. I've created a pattern but can't figure how to exclude the filter context for current year in the report.

Actual Qty Average = 
Var FirstSDate = CALCULATE(STARTOFYEAR('Date Table'[DateKey]), KEEPFILTERS('Date Table'[Delta Years]=0))
RETURN
CALCULATE(AVERAGEX(VALUES('Date Table'[Year]),[Actual Qty]),KEEPFILTERS('Date Table'[DateKey]<FirstSDate))

I need these results to appear in this report below when the specific product code and 2021 is selected from date table..

[amitchandak]

@davehus , Try a measure like with date table

Rolling 3 day = CALCULATE(Average('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,Day))

or

Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,Day)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,Day), filter(Sales,not(isblank(sum(Sales[Sales]))))))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[davehus]

Hi @amitchandak , Thanks for the reply 🙂 

I've got that bit figured, I just need it to appear in my report when I filter on the current year. The measure looks at the prior 3 years so I need the measure to ignore the filters on my report. Hope that makes sense.

D

[amitchandak]

@davehus , if you do not need a trend then above with

Rolling 3 day = CALCULATE(Average('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,Year), all(Date))

or //only at year level

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

If you need trend, means show three year when one is selected, you need an independent table, refer my video

https://www.youtube.com/watch?v=44fGGmg9fHI&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=18

[davehus · Accepted solution]

Thanks for your help @amitchandak , I figured it out in the end, I got the desired result with the pattern below.😁

3 Year Average = 
Var FirstSDate = CALCULATE(STARTOFYEAR('Date Table'[DateKey]), KEEPFILTERS('Date Table'[Delta Years]=0))
RETURN
CALCULATE(AVERAGEX(VALUES('Date Table'[Year]),[Actual Qty]),KEEPFILTERS('Date Table'[DateKey]<FirstSDate),REMOVEFILTERS('Date Table'[Year]))