calculate duration between date-time of consecutie date-time rows

logicscience2 — Sun, 31 Mar 2024 17:41:22 GMT

I have data of this format :

during import the data is sorted by date-time column then the Index column is added. So regardless how the table is sorted after import, the index can maintain a reference to sequential date-time values.

I am trying to add the duration-between calculated column which will calculate the difference between two rows based on the index order so in the picture above the first row would be blank or error, the second row would be 235 (minutes), the third would be 52 (minutes) and so on. These values would stay correctly calculated regardless of the ordering on the table.

Re: calculate duration between date-time of consecutie date-time rows

logicscience2 — Sun, 31 Mar 2024 18:19:51 GMT

This solution seems to work:

DurationBetween =
VAR CurrentDateTime = df[date-time]
VAR NextDateTime =
CALCULATE(
MIN(df[date-time]),
FILTER(
df,
df[date-time] > CurrentDateTime
)
)
RETURN
IF(
ISBLANK(NextDateTime),
BLANK(),
(NextDateTime - CurrentDateTime)*24*60
)

topic Re: calculate duration between date-time of consecutie date-time rows in DAX Commands and Tips

calculate duration between date-time of consecutie date-time rows

Re: calculate duration between date-time of consecutie date-time rows