Forum Discussion

Janica123's avatar
Janica123
Icon for Helper I rankHelper I
4 years ago
Solved

Calculate time difference in one column

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 ...
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

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:

 

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

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.