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 put the outcome into a new column named "Timediff". 

 

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

 

 

 

  • 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

     

6 Replies

  • 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.

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

      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
    • Janica123's avatar
      Janica123
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        Janica123
        Icon for Helper I rankHelper I
        4 years ago

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