Solved: Calculating time between separate rows based on an...

thehalfboy · ‎11-27-2023

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 of the world.

My data looks like this:

Requisition Number	Action Code	Action Date	Creation Date	Approved Date
RR8N3	SUBMIT	20/09/2023 15:03	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	20/09/2023 15:09	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	20/09/2023 15:28	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	22/09/2023 15:29	20/09/2023 15:03	16/10/2023 16:59
RR8N3	REJECT	29/09/2023 15:52	20/09/2023 15:03	16/10/2023 16:59
RR8N3	SUBMIT	05/10/2023 11:52	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	05/10/2023 12:01	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	05/10/2023 14:46	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 14:57	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 14:59	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 15:56	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 16:13	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 16:28	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 16:42	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	16/10/2023 16:44	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	16/10/2023 16:59	20/09/2023 15:03	16/10/2023 16:59

For each order, there may be multiple "SUBMIT" lines and multiple "APPROVE". I need to find two different bits of data from this:

The amount of time in hours between the final "SUBMIT" and the final "APPROVE"
The amount of time in hours between the penultimate "APPROVE" and the final "APPROVE"

The plan is to be able to show how much time would be saved by skipping to the final stage of the approval process, so the first bit of data shows how long it took for an order to pass through the approval hierarchy, while the second bit of data shows how long it would have taken if the order had gone to the final step in the chain right away.

Any help would be greatly appreciated, and stop me from pulling out my last few remaining hairs.

v-yifanw-msft · ‎12-04-2023

Hi @thehalfboy ,

Please follow these steps:

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

View solution in original post

thehalfboy · ‎11-28-2023

Thank you for the replies. I'm sorry, but I think in my urge to simplify my data, I took it too far, and so the responses aren't working right. There are actually multiple different Requisition numbers, and so the calculation needs to find the "last submit" to "last approval" and "penultimate approval" to "last approval" for each requisition number in the table. Hopefully the data below is more like what is needed.

Requisition Number	Action Code	Action Date	Creation Date	Approved Date
RR8N2	SUBMIT	24/03/2023 09:37	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	18/04/2023 11:03	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	18/04/2023 11:03	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	18/04/2023 11:08	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	18/04/2023 12:56	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	18/04/2023 13:07	24/03/2023 07:30	02/05/2023 18:06
RR8N2	REQUISITION WITHDRAW	19/04/2023 16:36	24/03/2023 07:30	02/05/2023 18:06
RR8N2	SUBMIT	19/04/2023 16:43	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	20/04/2023 07:12	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	25/04/2023 11:41	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	25/04/2023 12:40	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	25/04/2023 12:41	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	27/04/2023 12:44	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	27/04/2023 12:45	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	27/04/2023 12:46	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	27/04/2023 16:09	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	27/04/2023 17:12	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	02/05/2023 17:16	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	02/05/2023 17:23	24/03/2023 07:30	02/05/2023 18:06
RR8N2	APPROVE	02/05/2023 18:06	24/03/2023 07:30	02/05/2023 18:06
RR8N3	SUBMIT	20/09/2023 15:03	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	20/09/2023 15:09	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	20/09/2023 15:28	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	22/09/2023 15:29	20/09/2023 15:03	16/10/2023 16:59
RR8N3	REJECT	29/09/2023 15:52	20/09/2023 15:03	16/10/2023 16:59
RR8N3	SUBMIT	05/10/2023 11:52	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	05/10/2023 12:01	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	05/10/2023 14:46	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 14:57	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 14:59	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 15:56	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 16:13	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 16:28	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	10/10/2023 16:42	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	16/10/2023 16:44	20/09/2023 15:03	16/10/2023 16:59
RR8N3	APPROVE	16/10/2023 16:59	20/09/2023 15:03	16/10/2023 16:59

v-yifanw-msft · ‎12-04-2023

Hi @thehalfboy ,

Please follow these steps:

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

v-yifanw-msft · ‎11-27-2023

Hi @thehalfboy ,

Please follow these steps:

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 )

sort APPROVE

the penultimate "APPROVE" and the final "APPROVE" =
RANKX (
    FILTER ( 'Table', 'Table'[Action Code] = "APPROVE" ),
    'Table'[Action Date],
    ,
    DESC
)

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

amitchandak · ‎11-27-2023

@thehalfboy , Create a column and following measure

New column
Approve Rank =
RANKX (
FILTER (
'Table',
'Table'[Requisition Number] = EARLIER ( 'Table'[Requisition Number] )
&& 'Table'[Action Code] = "APPROVE"
),
'Table'[Action Date],
,
DESC,
Dense
)

New Measure

Final Submit Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
allselected('Table'),
'Table'[Requisition Number] = max ( 'Table'[Requisition Number] )
&& 'Table'[Action Code] = "SUBMIT"
)
)

Final Approve Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
allselected('Table'),
'Table'[Requisition Number] = max ( 'Table'[Requisition Number] )
&& 'Table'[Action Code] = "APPROVE"
)
)

Time Between Submit and Approve (Hours) =
DATEDIFF (
[Final Submit Date],
[Final Approve Date],
HOUR
)

Penultimate Approve Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
allselected('Table'),
'Table'[Requisition Number] = max ( 'Table'[Requisition Number] )
&& 'Table'[Approve Rank] = 2
)
)

DATEDIFF (
[Penultimate Approve Date],
[Final Approve Date],
HOUR
)

For the first rank measure, you can use the new rank function to create measure

)

Penultimate Approve Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
'Table',
'Table'[Requisition Number] = EARLIER ( 'Table'[Requisition Number] )
&& 'Table'[Approve Rank] = 2
)
)

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Calculating time between separate rows based on another column

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly