Need help: Plot values from last n year over selected year

[Anonymous]

I want to filter a line visual with a slicer. This slicer filters the year from a seperate date table. This date table is connected to the data table.

I want to plot 3 lines. The green and black are showing the actual and last year values. Those are correct.

The red should show the minimum of the last 4 years. I cant get this done. It is somehow not taking the correct dates.  

measure = CALCULATE(MIN(data[Value]);DATESINPERIOD('Calendar'[Date];LASTDATE('Calendar'[Date]);-4;YEAR))

[vanessafvg]

@Anonymous  from what i can see it is plotting the dates, the issue is with your avg?

what are you expecting to see, i can't quite understand what the issue is, how is it not showing what you expect?

[Anonymous]

Thank you for you answer.

I want the minimum of the last 4 years plotted for each day. Right now the red line is flat and it seems, that the calculation is wrong, because the second part of the calculate function doesnt work the way I want to.

I checked it by counting the values in a table. Per day there are around 96 values, My measure should include the last 4 years so I expect 384 values. 

measure = CALCULATE(COUNT(data[Value]);DATESINPERIOD('Calendar'[Date];LASTDATE('Calendar'[Date]);-4;YEAR))

If I select 2019 on the slicer I expect to see following:

date                   measure

2019-01-01       384 (4*96)

I get a very different result. 

[vanessafvg]

@Anonymous  its quite difficult to see the problem without looking at the data, are you able to share data?

also when you say you expect to see 384 (on the count), would that be plotted 384 a flat line?  it would be best to explain what you are trying to achieve, not technically but from a business perspective.

[Anonymous]

Left you can see the table and right the slicer.

the first two columns are exactly doing, what I expect. The last column (last4years) is showing a wrong result.

[vanessafvg]

ok so just to be clear @Anonymous 

measure = CALCULATE(MIN(data[Value]);DATESINPERIOD('Calendar'[Date];LASTDATE('Calendar'[Date]);-4;YEAR))

is the column last4years?

[Anonymous]

Yes.

[Anonymous · Accepted solution]

Got it running with following measure:

LJ_Max =

VAR Temp1=

UNION(

ROW("test1"; MAX(data[Value]));

ROW("test2"; CALCULATE(MAX(data[Value]);DATEADD('Calendar'[Date];-1;YEAR)));

ROW("test3"; CALCULATE(MAX(data[Value]);DATEADD('Calendar'[Date];-2;YEAR)));

ROW("test4"; CALCULATE(MAX(data[Value]);DATEADD('Calendar'[Date];-3;YEAR)));

ROW("test5"; CALCULATE(MAX(data[Value]);DATEADD('Calendar'[Date];-4;YEAR)));

)

RETURN

MAXX(Temp1; [test1])

If you want to have the minimum just replace all the max and maxx functions with min or minxx