Use yearaverage as target line

[Anonymous]

Hi all,

i'm facing a new issue. The customer would like to see the average of 2 years ago as a constant targetline in the chart. 

So i created the average of 2 years ago:

Norm eigenvertraging = 
    CALCULATE(AVERAGE('Vertraging'[eigenvertraging])
    ,DATEADD('Date'[Date], -2, YEAR)
    )

But when i use this, it obviously shows the average per week in the chart. I tried using ALL, ALLSELECTED but no luck unfortunately

So my question is: How can i turn this into the average over the whole year so i can use it as a targetline?

Tnx!

[amitchandak · Accepted solution]

@Anonymous , Try like

Norm eigenvertraging =
var _1 = maxx(allselected('Date'), 'Date'[Date])
var _2= year(_1) -2
return
CALCULATE(AVERAGE('Vertraging'[eigenvertraging])
,filter(all('Date') year('Date'[Date]) = _2
))

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

You could try the following measure:

TEST1 = 
VAR LASTTWOYEARDAY =
    DATE ( YEAR ( TODAY () ) - 2, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR TEST1 =
    CALCULATE (
        AVERAGE ( Vertraging[eigenvertraging] ),
        FILTER (ALL('Date'),'Date'[Date]<=LASTTWOYEARDAY)
    )
RETURN
    TEST1

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Best Regards

Lucien

[amitchandak · Accepted solution]

@Anonymous , Try like

Norm eigenvertraging =
var _1 = maxx(allselected('Date'), 'Date'[Date])
var _2= year(_1) -2
return
CALCULATE(AVERAGE('Vertraging'[eigenvertraging])
,filter(all('Date') year('Date'[Date]) = _2
))