Subtract value from earlier rows depending on Category

[v2]

Hi,

I am new to power Bi and I have seen a couple of solutions to this but have been unable to incorporate this in my solution.

Attached  table has a desired value I want to achieve using measures or any  any other way in Power BI

• The date row has to be sorted in ascending order
• the desired value should look to see if there is an earlier date for the row category  and subtract the current value form the previous value. If there is no previous date, it should list the same  value.  

Date	Category	Value	Desired value
10-03-13	CAT-A	9456	no value before this date so the value should remain as 9456
16-03-13	CAT-B	15238	no value before this date  so value remains the same 15238
03-04-13	CAT-A	12657	= 12657-9456
29-01-14	CAT-C	3452	=3452
30-03-14	CAT-A	14324	=14324 - last CAT-A value
01-09-15	CAT-B	18234	=18234 -15238
04-02-15	CAT-C	15234	=15234-3452
15-10-15	CAT-D	4561	=4561

Hope this makes sense!

Thanks

[CNENFRNL · Accepted solution]

[Damkilde1986]

Great solution! Worked very well on my dataset aswell. Is there a way to create a similar solution in the Power Query Editor? It seems that due to large amount of data i keep getting errors.

Much appreciated - thanks!

[v2]

Excellent ! exactly what i needed. Thank you.

[Greg_Deckler]

@v2 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....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

Also: https://community.powerbi.com/t5/Quick-Measures-Gallery/Mean-Time-Between-Failure-MTBF/m-p/625082#M3...