First value become second value.

[Anonymous]

Hi 

Please help me with DAX mentioned below.

Date	Cat	Value	Expecting Result
21/03/2012	A	100	0
15/04/2016	A	200	100
10/102020	A	300	200
21/03/2012	B	1000	0
13/7/2014	B	2000	1000
15/04/2016	B	3000	2000
06/062020	B	4000	3000

Thank you

[amitchandak · Accepted solution]

@Anonymous , Try a new column like

new column =
var _max = maxx(filter(table, [Date] < earlier([Date]) && [Cat] =earlier([Cat])),[Date])
return
maxx(filter(table, [Date] = _max && [Cat] =earlier([Cat]) ),[value])

[Anonymous]

Thank you so much @amitchandak