Substract from Different Rows on a Table

Anonymous 

You need to first add an index column in Power Query then add the following DAX column to the table:

Time Diff = 

var __currtime = Table2[Start Time]
var __previoustime  =  MAXX( FILTER( Table2 , Table2[Index] =  EARLIER(Table2[Index]) - 1), Table2[End Time]) 
var _Diff = DATEDIFF(+__previoustime , __currtime, SECOND )
return
_Diff
    

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Fowmy the Var only captures the Measures, is there a way to use the columns?

Hi, Anonymous 

Based on your descripton, I created data to reproduce your sceanrio. The pbix file is attached in the end.

Table:

You may create an index column in Power Query and then create a measure or a column as below.

Measure:

Result Measure = 
var totalseconds =  
DATEDIFF(
    CALCULATE(
        MAX('Table'[Status End Datetime]),
        FILTER(
            ALL('Table'),
            [Index]=MAX('Table'[Index])-1
        )
    ),MAX('Table'[Status Start Datetime]),SECOND
)
var m = 
COALESCE(INT(DIVIDE(totalseconds,60)),0)
var s = COALESCE(MOD(totalseconds,60),0)
return
m&":"&s

Calculated column:

Result Column = 
var totalseconds =  
DATEDIFF(
    CALCULATE(
        MAX('Table'[Status End Datetime]),
        FILTER(
            ALL('Table'),
            [Index]=EARLIER('Table'[Index])-1
        )
    ),[Status Start Datetime],SECOND
)
var m = 
COALESCE(INT(DIVIDE(totalseconds,60)),0)
var s = COALESCE(MOD(totalseconds,60),0)
return
m&":"&s

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-alq-msft  Can you do the same but with the timestamp in a different order and different dates? I see that it works in yours but mine has different dates and times and does not show the same results.