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.

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

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        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