Calculate time difference in one column

[Janica123]

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!

[Whitewater100]

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:

[Janica123]

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!! 

[Janica123]

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

[v-janeyg-msft · Accepted solution]

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

[Whitewater100]

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

[Whitewater100]

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.