excel - Compare values previous date and second previous date in PowerPivot -

i'm new powerpivot , dax. i've followed on-line tutorials. have small problem can't solve. have following data:

date instrument value 2016-07-27 100 2016-07-27 b 98 2016-07-26 102 2016-07-25 b 99

for each date calculate difference (profit/loss) in value between recent date , second recent date. data above following:

date instrument value profit/loss 2016-07-27 102 2 ([val. inst. 2016-07-27]-[val. inst. 2016-07-26]) 2016-07-27 b 98 -1 ([val. inst. b 2016-07-27]-[val. inst. b 2016-07-25]) 2016-07-26 100 2016-07-25 b 99

i have tried dax find second largest date using =earlier([date])but haven't managed work. second largest date maybe able find value corresponding date. suggestions how solved?

in end came solution in 3 steps (the steps can combined 1 step). first rank dates, recent being 1 , second recent being 2. after retrieve value second recent day each row. calculate difference between values current row , value second recent day compared row's date.

to rank dates used following:

=rankx(filter(all(table);earlier([instrument])=[instrument]);[date];;false())

explanation think dax formula doing. rankx works taking table , rank values in column in table. above i've used filtered table table. filtered table creates new table each row containing same instrument instrument particular row. first row, filtered table below.

date instrument value 2016-07-27 100 2016-07-26 102

the dates in filtered table ranked.

date instrument value rank 2016-07-27 100 1 2016-07-26 102 2

using rank pull out second recent dates value each row based on current row's rank-1.

value second recent date = calculate(max([value]);filter(table;earlier([instrument])=[instrument] && [date rank]= earlier([date rank]))

finally calculate difference:

pnl = [value] - [value second recent date]

i'm not sure earlier doing think sort of iterative process.