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

     

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