Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
2 years ago
Solved

Find duration between bookings

Bit abstract but this is the picture :

So we want to compare MvT 311 with either 321 or 343.

And what we want to know is how long it took for 311 going to 321/343.

 

So the Mat/Batch combination combines these 2.

What I was thinking of is a way for a Mat/Batch combi to 'lookup' the Time when it is 311 and a second field to see what the time is when Mvt is 321 or 343.

And then difference between these 2 fields would be how long it took.

 

And an extra handicap could be that 311 is alway 1 line but the 321/343 could more lines with different times.

If it can not be done for every time them maybe the last time in 321/343 would be the best.

 

Can this be done in this way?

 

I have the sample data in the list here below for testing :

Mat/BatchMvTTime LongSum of Qty
P0990814679-02402214131112-03-24 13:14-1280
P0990814679-02402214132112-03-24 12:221280
P0990814679402144131108-03-24 08:53-2800
P0990814679402144132107-03-24 12:41960
P0990814679402144132107-03-24 12:42960
P0990814679402144132107-03-24 12:42880
P0991112673-03402264131111-03-24 07:50-1920
P0991112673-03402264132108-03-24 12:451920
P0991412773-02402064131114-03-24 09:01-3510
P0991412773-02402064134312-03-24 14:183510
P0991412773-02402064231113-03-24 09:14-3970
P0991412773-02402064234312-03-24 18:373970

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi rpinxt ,

    I do some changes on my .pbix file. This is my latest DAX code.

    MEASURE =
VAR _currentMat =
    SELECTEDVALUE ( 'Table'[Mat/Batch] )
VAR _currentTimelong =
    MAX ( 'Table'[Time Long] )
VAR _DateTime311 =
    CALCULATE (
        MAX ( 'Table'[Time Long] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MvT] = "311"
                && 'Table'[Mat/Batch] = _currentMat
        )
    )
RETURN
    IF (
        MAX ( 'Table'[MvT] ) <> "311",
        DATEDIFF ( _currentTimelong, _DateTime311, HOUR )
    )

    You can also change HOUR to DAY and so on. Then you can get the result.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • rpinxt's avatar
    rpinxt
    2 years ago

    Thanks Anonymous !

    This looks good however I think something went wrong in creating your data because all dates shoud be in 2024. Can see some 2013 and 2014 at the end of the list.

     

    Anyway the method works. Just a pitty the datediff only does full hours.

    Makes the calculation not really accurate.

     

    But I was able to fix it by just subtracting the 2 variables.

    Then setting the format of the field to (hh:nn:ss) and that looks good :

    Thanks for putting me on the right track with this solution 😄

5 Replies

  • rpinxt's avatar
    rpinxt
    Solution Sage
    2 years ago

    Any other thoughts on how to accomplish this would also be welcome....

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi rpinxt ,

    Based on your problems, here are my answers.

    I create a table as you mentioned.

    Then I create a Calculated column.

    TimeDiff =
VAR Time311 = [Time Long]
VAR Time321 =
    CALCULATE ( MAX ( 'Table'[Time Long] ), 'Table'[MvT] = "321" )
VAR Time343 =
    CALCULATE ( MAX ( 'Table'[Time Long] ), 'Table'[MvT] = "343" )
RETURN
    IF ( [MvT] = "311", Time321 - Time311, Time343 - Time311 )

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • rpinxt's avatar
      rpinxt
      Solution Sage
      2 years ago

      Thanks Anonymous , so not with dax but with a calculated column.

      But the TimeDiff looks in a non usuable format. 1/25/1775??

      Would there be a way to convert this to hours (or days and hours) so that we actually know how look it took to go from 311 to 321/343 ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi rpinxt ,

        I do some changes on my .pbix file. This is my latest DAX code.

        MEASURE =
VAR _currentMat =
    SELECTEDVALUE ( 'Table'[Mat/Batch] )
VAR _currentTimelong =
    MAX ( 'Table'[Time Long] )
VAR _DateTime311 =
    CALCULATE (
        MAX ( 'Table'[Time Long] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[MvT] = "311"
                && 'Table'[Mat/Batch] = _currentMat
        )
    )
RETURN
    IF (
        MAX ( 'Table'[MvT] ) <> "311",
        DATEDIFF ( _currentTimelong, _DateTime311, HOUR )
    )

        You can also change HOUR to DAY and so on. Then you can get the result.

         

         

         

        Best Regards

        Yilong Zhou

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.