Forum Discussion

thehalfboy's avatar
thehalfboy
Helper I
2 years ago
Solved

Calculating time between separate rows based on another column

Good afternoon,   I've come across a thorny little problem that I can't work out the logic on. Ideally I'd like to solve this woth DAX but if it's easier within Power Query that wouldn't be the end...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi thehalfboy ,

    Please follow these steps:

    1.        add new column

    Column = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Requisition Number] = EARLIER ( 'Table'[Requisition Number] )
    )
)


    the final "SUBMIT" and the final "APPROVE" = 
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER (
            'Table',
            'Table'[Action Code] = "APPROVE"
                && 'Table'[Column] = EARLIER ( 'Table'[Column] )
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER (
            'Table',
            'Table'[Action Code] = "SUBMIT"
                && 'Table'[Column] = EARLIER ( 'Table'[Column] )
        )
    )
RETURN
    DATEDIFF ( _2, _1, HOUR )

    2.    sort APPROVE

    sort = 
RANKX (
    FILTER (
        'Table',
        'Table'[Action Code] = "APPROVE"
            && 'Table'[Column] = EARLIER ( 'Table'[Column] )
    ),
    'Table'[Action Date],
    ,
    DESC
)

    3.     the penultimate "APPROVE" and the final "APPROVE"

    the penultimate "APPROVE" and the final "APPROVE" 1 = 
VAR _actDate =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER (
            'Table',
            'Table'[sort] = 1
                && 'Table'[Column] = SELECTEDVALUE ( 'Table'[Column] )
        )
    )
VAR _actDate2 =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER (
            'Table',
            'Table'[sort] = 2
                && 'Table'[Column] = SELECTEDVALUE ( 'Table'[Column] )
        )
    )
RETURN
    DATEDIFF ( _actDate2, _actDate, MINUTE )

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

    If it does not help, please provide more details with your desired out put and pbix file without privacy information.

     

    Best Regards,

    Yifan Wang

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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi thehalfboy ,

Please follow these steps:

  1.        add new column

the final "SUBMIT" and the final "APPROVE" = 
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER ( 'Table', 'Table'[Action Code] = "APPROVE" )
    )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER ( 'Table', 'Table'[Action Code] = "SUBMIT" )
    )
RETURN
    DATEDIFF ( _2, _1, HOUR )
  1.        sort APPROVE

the penultimate "APPROVE" and the final "APPROVE" =
RANKX (
    FILTER ( 'Table', 'Table'[Action Code] = "APPROVE" ),
    'Table'[Action Date],
    ,
    DESC
)
  1.         the penultimate "APPROVE" and the final "APPROVE"
the penultimate "APPROVE" and the final "APPROVE" = 
   var _actDate = CALCULATE(
       MAX('Table'[Action Date]),
       FILTER('Table','Table'[sort] = 1)
   )
   var _actDate2 = CALCULATE(
       MAX('Table'[Action Date]),
       FILTER('Table','Table'[sort] = 2)
   )
  RETURN
     DATEDIFF(_actDate2,_actDate,MINUTE
         )

 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Yifan Wang

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