I'm working in a dashboard that control some kpis at my company. Now, I need to compare every result with the previous one, according to employee number. In the sample below, I show a little exemple of my data, and the expected result in the last column (previous score). I've tryed to solve that using a lot os calculated columns. I got close using the following:
PreviousScore =
VAR EMPNUMBER = BASE[Employee Number]
VAR REF = BASE[Score]
RETURN
CALCULATE(LASTNONBLANK(BASE[Employee Number];EMPNUMBER);FILTER(BASE;BASE[Employee Number]=EMPNUMBER);FILTER(BASE;BASE[Date]<EARLIER(BASE[Date])))
Employee Number Date Score Previous score
1234 01/01/2019 1 BLANK
1235 01/01/2019 4 BLANK
1236 01/01/2019 2 BLANK
1288 01/01/2019 0 BLANK
1259 01/01/2019 0 BLANK
1234 01/02/2019 3 1
1235 01/02/2019 4 4
1236 01/02/2019 1 2
1288 01/02/2019 2 0
1259 01/02/2019 4 0
1234 01/03/2019 1 3
1235 01/03/2019 2 4
1236 01/03/2019 3 1
1288 01/03/2019 0 2
1259 01/03/2019 1 4
1234 01/04/2019 2 1
1235 01/04/2019 3 2
1236 01/04/2019 8 3
1288 01/04/2019 BLANK 0
1259 01/04/2019 BLANK 1
I hope someone could help with this issue.
LW