topic Re: Calculate time difference in one column in DAX Commands and Tips

Calculate time difference in one column

Janica123 — Tue, 26 Apr 2022 12:19:07 GMT

Hi everyone,

My data looks similar to the first three columns.

Now, what I need to do is to calculate the timedifference of the values in column "Time" for each order and date and put the outcome into a new column named "Timediff".

Is there an easy way to do this? Thank you for your help!

Re: Calculate time difference in one column

Whitewater100 — Tue, 26 Apr 2022 13:51:02 GMT

Hi:

You can try this calculated column. I named your table "Data". Where a column had a time like 8:20, I changed each Column with time to DATE/TIME data type.

I hope this solves the question. Thanks..

Time Difference =

var PreviousRow = LOOKUPVALUE(Data[Time],

Data[Index],

Data[Index] -1

)

var Mvalue = CALCULATE(MIN(Data[Time]), ALLEXCEPT(Data,Data[Order]))

var Last = IF(Data[Time] = Mvalue, BLANK(), PreviousRow - Data[Time])

var result = IF(Last = BLANK(), BLANK(),

DATEDIFF(Data[Time], PreviousRow,MINUTE))

return

result

Here is an image with the steps and then all folded into one calculation:

Re: Calculate time difference in one column

Whitewater100 — Tue, 26 Apr 2022 13:52:41 GMT

I forgot to mention, I added an index column in Power Query.

Transform Data > Add New Column > Index Column > Index From 1. That helps getting previous row.

Re: Calculate time difference in one column

Whitewater100 — Tue, 26 Apr 2022 16:57:02 GMT

Hi:

If you want result to be positive the code would be the same until the last part.

here is the last line if you want to see minutes in positive values:

result*-1

Re: Calculate time difference in one column

Janica123 — Wed, 27 Apr 2022 06:13:58 GMT

Thank you very much for your help!

But now there is one issue left. My data looks now similar to this.

The problem is, that the time difference should be based on one order only and should also include the datediff. Meaning that if I filter now for order A the data looks like this. The green colored columns show how it should look like.

If you have any suggestions I would be very happy!!

Re: Calculate time difference in one column

Janica123 — Wed, 27 Apr 2022 06:17:36 GMT

Is it possible that we need to solve this with a measure not a calculated column?

Re: Calculate time difference in one column

v-janeyg-msft — Fri, 29 Apr 2022 08:09:31 GMT

Hi, Janica123

Yes, you need to create the datetime measure and use it to create datediff measure to display your results.

Like this:

Datetime = SELECTEDVALUE('Table'[Date])+SELECTEDVALUE('Table'[Time])

Measure = VAR a = MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Index] < SELECTEDVALUE ( 'Table'[Index] ) && [Order] = SELECTEDVALUE ( 'Table'[Order] ) ), [Datetime] ) RETURN DATEDIFF ( a, [Datetime], MINUTE )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey