How to create a calculated column to subtract previous row value?

[ashrafkotb]

Hello,

I need to create a calculated column that subtracts the current row value from the previous row value based on date and customer name. For example:

Date	Value	Customer	Calc_column (expected value)
01-04-2020	100	A
01-04-2020	400	B
01-04-2020	500	C
02-04-2020	120	A	20
02-04-2020	440	B	40
02-04-2020	560	C	60

Thanks in advance!

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

So in your case, something like: 

Calc_column = 
  VAR __PreviousDate = MAXX(FILTER('Table',[Date] < EARLIER([Date]) && [Customer] = EARLIER([Customer])),[Date])
  VAR __PreviousValue = MAXX(FILTER('Table',[Date] = __PreviousDate) && [Customer] = EARLIER([Customer])),[Date])
RETURN
  [Value] - __PreviousValue
  

[ashrafkotb]

Getting a Syntax error for this formula.

[amitchandak]

@ashrafkotb ,

Try new columns like

max date = maxx(filter(Table, table[Date]<earlier(Table[Date]) && table[Customer]=earlier(Table[Customer])),Table[Date])
diff = Table[value] - maxx(filter(Table, table[Date]=earlier(Table[max date]) && table[Customer]=earlier(Table[Customer])),Table[value])

[az38 · Accepted solution]

Hi @ashrafkotb 

try

Calc_column = 
var _thisDate = Table[Date]
var _prevDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Customer]), Table[Date] < _thisDate )

RETURN
Table[Value] - CALCULATE(MAX(Table[Value]), ALLEXCEPT(Table, Table[Customer]), Table[Date] = _prevDate )

[ashrafkotb]

This worked, thank you.