Forum Discussion

andreazambon's avatar
andreazambon
Helper V
3 years ago

Delta value between 2 weeks

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.

Need Help

5 Replies

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

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