DAX measure to subtract current row value with next row

[Anonymous]

Hi All,

I am trying to create a measure and add it to a table visual to subtract current row value with the next row value. My table looks like below - 

I have slicers for ID, County & Org  and the table visual is filetered based on the selection and my LeadTime is the datediff between StartDate and EndDate and I am trying to calculate LeadRange which subtracts current row LeadTime with the Next row LeadTime

ID	Case	County	Org	StartDate	EndDate	LeadTime	LeadRange	LeadRange Formula
1	A	NC	Org1	4/1/2020	4/20/2020	19	1	19-18
1	B	NC	Org1	4/2/2020	4/20/2020	18	0	18-18
1	B	NC	Org1	4/2/2020	4/20/2020	18	-1	18-19
2	A	SC	Org2	4/1/2020	4/20/2020	19	1	19-18
2	B	SC	Org2	4/2/2020	4/20/2020	18	1	18-17
2	C	SC	Org2	4/3/2020	4/20/2020	17		blank

I've tried multiple things and none worked. Any assistance is highly appreciated.

Regards,

Rohith

[V-pazhen-msft · Accepted solution]

@Anonymous 

An index column is required, then you can create the measure:

Measure = SUM([LeadTime])-CALCULATE(SUM([LeadTime]),FILTER(ALLSELECTED('Table'),[Index]=MAX([Index])+1))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Portrek]

Hi, rohithgndriv

You need to create a "column index" in power query, and after you put a new column in dax with the code below.

Difference =
var curIndex = TABLE[Index]
var curVal = TABLE[LeadTime]
var oldINDEX = TABLE[Index] + 1
var oldVal = CALCULATE(
FIRSTNONBLANK(TABLE[LeadTime],1),
FILTER(TABLE,
TABLE[Index]=oldINDEX))
return IF(CONTAINS(TABLE,TABLE[Index],oldINDEX), curVal-oldVal, 0)

Best regards.