Delta value between 2 weeks

[andreazambon]

Hi there. 

I'm trying to calculate the delta% between th last week agree with my filter anche le last-1 week.

I try with this:

Delta%PerditaLast = 
VAR PercMaxWeek= if(MAX(DateTable[week]) > DateTable[ThisWeek],calculate('SAPProdEvent-2'[%Perdita_RIL], FILTER(DateTable,DateTable[week]=DateTable[ThisWeek])),calculate('SAPProdEvent-2'[%Perdita_RIL], FILTER(DateTable,DateTable[week]=MAX(DateTable[week]))))
VAR PercLast1Week = if(MAX(DateTable[week]) > DateTable[ThisWeek],calculate('SAPProdEvent-2'[%Perdita_RIL], FILTER(DateTable,DateTable[week]=DateTable[ThisWeek])),calculate('SAPProdEvent-2'[%Perdita_RIL], FILTER(DateTable,DateTable[week]=(MAX(DateTable[week])-1))))
//VAR Delta = PercMaxWeek-PercMinWeek
RETURN 
    PercMaxWeek-PercLast1Week

But probably the code 

MAX(DateTable[week])-1)

is not correct. 

Also it gives me an error: cannot compare a text type and a number type. 

Actually, I just copy the formula that I use to calcolate the %Delta with another percentage measure, but in that case i calculate the MAX and the MIN week of my filter. 

Some suggestion? 

Thanks.

[lbendlin]

Please show a couple of rows of your "DateTable" table.

[andreazambon]

[lbendlin]

var thisweek = max(DateTable[Week])
var prevweek = calculate(max(DateTable[Week]),all(DateTable),DateTable[Week])<thisweek)

[andreazambon]

Thanks for your reply, I think that I can use this to my case. 

Instead of this solution, what if I want to show the difference between a week and the previous one?

For example, in WK15 I want to show the difference between WK 15 and WK14, in WK 14 the difference between WK 14 and WK 13.. 

[lbendlin]

you would use the same approach. Remember that "MAX"  is dependent on the current filter context, and it has a different meaning in different parts of your visual. That way you can write a single measure that can  compare a value with its "previous"  incarnation.  (Note that the new windowing functions can also be used if you are looking for alternatives)