M Query to return time difference between two cells in the same column

Hi Anonymous 

Which method do you use?

from this statement:

I have been able to do this a couple other ways (DAX formulas and creating two different tables with index values)

It seems you use a complex method including creating another table.

But i used to slove this kind of problem with simple method below:

create calculated columns:

Column_Previous = CALCULATE(MAX(Sheet2[time]),FILTER(Sheet2,Sheet2[time]<EARLIER(Sheet2[time])))

diff_s = DATEDIFF([Column_Previous],[time],SECOND)

Or you could create a measure (measure will be better for performance generally, but measures can't be added into slicer visual)

Measure =
VAR Previous =
    CALCULATE (
        MAX ( Sheet2[time] ),
        FILTER ( ALLSELECTED ( Sheet2 ), Sheet2[time] < MAX ( Sheet2[time] ) )
    )
RETURN
    DATEDIFF ( Previous, MAX ( Sheet2[time] ), SECOND )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.