Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
thehalfboy
Helper I
Helper I

Calculating time between separate rows based on another column

‎11-27-2023 07:37 AM

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 NumberAction CodeAction DateCreation DateApproved Date
RR8N3SUBMIT20/09/2023 15:0320/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE20/09/2023 15:0920/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE20/09/2023 15:2820/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE22/09/2023 15:2920/09/2023 15:0316/10/2023 16:59
RR8N3REJECT29/09/2023 15:5220/09/2023 15:0316/10/2023 16:59
RR8N3SUBMIT05/10/2023 11:5220/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE05/10/2023 12:0120/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE05/10/2023 14:4620/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 14:5720/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 14:5920/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 15:5620/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 16:1320/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 16:2820/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 16:4220/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE16/10/2023 16:4420/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE16/10/2023 16:5920/09/2023 15:0316/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.

Labels:
Message 1 of 5
311 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to thehalfboy

‎12-04-2023 10:36 PM

Hi @thehalfboy ,

Please follow these steps:

  1.        add new column

vyifanwmsft_0-1701757993779.png

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

vyifanwmsft_1-1701758027775.png

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

vyifanwmsft_2-1701758105262.png

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"

vyifanwmsft_3-1701758147417.png

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

Calculating time between separate rows based on another column.pbix
Message 5 of 5
215 Views
0
4 REPLIES 4
thehalfboy
Helper I
Helper I

‎11-28-2023 06:03 AM

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 NumberAction CodeAction DateCreation DateApproved Date
RR8N2SUBMIT24/03/2023 09:3724/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE18/04/2023 11:0324/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE18/04/2023 11:0324/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE18/04/2023 11:0824/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE18/04/2023 12:5624/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE18/04/2023 13:0724/03/2023 07:3002/05/2023 18:06
RR8N2REQUISITION WITHDRAW19/04/2023 16:3624/03/2023 07:3002/05/2023 18:06
RR8N2SUBMIT19/04/2023 16:4324/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE20/04/2023 07:1224/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE25/04/2023 11:4124/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE25/04/2023 12:4024/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE25/04/2023 12:4124/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE27/04/2023 12:4424/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE27/04/2023 12:4524/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE27/04/2023 12:4624/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE27/04/2023 16:0924/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE27/04/2023 17:1224/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE02/05/2023 17:1624/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE02/05/2023 17:2324/03/2023 07:3002/05/2023 18:06
RR8N2APPROVE02/05/2023 18:0624/03/2023 07:3002/05/2023 18:06
RR8N3SUBMIT20/09/2023 15:0320/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE20/09/2023 15:0920/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE20/09/2023 15:2820/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE22/09/2023 15:2920/09/2023 15:0316/10/2023 16:59
RR8N3REJECT29/09/2023 15:5220/09/2023 15:0316/10/2023 16:59
RR8N3SUBMIT05/10/2023 11:5220/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE05/10/2023 12:0120/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE05/10/2023 14:4620/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 14:5720/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 14:5920/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 15:5620/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 16:1320/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 16:2820/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE10/10/2023 16:4220/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE16/10/2023 16:4420/09/2023 15:0316/10/2023 16:59
RR8N3APPROVE16/10/2023 16:5920/09/2023 15:0316/10/2023 16:59
Message 4 of 5
250 Views
0
Anonymous
Not applicable
In response to thehalfboy

‎12-04-2023 10:36 PM

Hi @thehalfboy ,

Please follow these steps:

  1.        add new column

vyifanwmsft_0-1701757993779.png

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

vyifanwmsft_1-1701758027775.png

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

vyifanwmsft_2-1701758105262.png

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"

vyifanwmsft_3-1701758147417.png

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.

 

 

 

Calculating time between separate rows based on another column.pbix
Message 5 of 5
216 Views
0
Anonymous
Not applicable

‎11-27-2023 10:33 PM

Hi @thehalfboy ,

Please follow these steps:

  1.        add new column

vyifanwmsft_0-1701152939122.png

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

vyifanwmsft_1-1701153041187.png

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.

Calculating time between separate rows based on another column.pbix
Message 3 of 5
269 Views
0
amitchandak
Super User
Super User

‎11-27-2023 07:41 PM

@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
)
)







Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 2 of 5
275 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All