Calculate difference of value in current row from value in previous row.

[bob57]

Hello. This seems simple enough but has offered me a bit of a challenge. I am seeking a DAX measure to solve this problem. I am also curious as to how a calculated column could do the same.

Eaxample data table:

Date	Cumulative Sold
1/1/2020	3
1/2/2020	5
1/3/2020	10
1/4/2020	14
1/5/2020	15
1/6/2020	20

I need to create a table visualization that displays the two columns from the data table (easy enough) and develop a DAX measure for the third column as shown below.

Date	Cumulative Sold	Daily Num Sold
1/1/2020	3	3
1/2/2020	5	2
1/3/2020	10	5
1/4/2020	14	4
1/5/2020	15	1
1/6/2020	20	5

Thank you for your time and effort.

Bob

[Greg_Deckler · Accepted solution]

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...

In your case:

Daily Num Sold Measure =
  VAR __Current = MAX('Table'[Cumulative Sold])
  VAR __Date = MAX('Table'[Date])
  VAR __PreviousDate = MAXX(FILTER('Table',[Date] < __Date),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date] = __PreviousDate),[Cumulative Sold])
RETURN
  __Current - __Previous

[bob57]

My apologies for the very poor colimn alignment.