Calculating Date/Timer Differences Dynamically

[therightblue]

Good Afternoon,

 I’m stuck on a DAX issue right now.

My organization is seeking to create a report that tracks how quickly we approve quotes from the time they are submitted by Sales.

IT has provided us with a report- from our ERP system that shows timestamps of each approval level for each quote/quote version:

Quote Number	Quote Version	Response	Responder User Name	Submission Date	Response Date	Last Update Date
100284435	1	APPROVED	Alex Dallas	08-06-2023 12:27:20	08-06-2023 12:28:18	08-06-2023 12:28:18
100287137	2	APPROVED	Alex Dallas	08-06-2023 12:33:40	08-06-2023 12:34:20	08-06-2023 12:37:32
100287137	2	APPROVED	Jon Woo	08-06-2023 12:33:40	08-06-2023 12:35:36	08-06-2023 12:37:32
100287137	2	APPROVED	Christopher Prescott	08-06-2023 12:33:40	08-06-2023 12:37:32	08-06-2023 12:37:32
100289144	1	APPROVED	Alex Dallas	08-06-2023 12:42:44	08-06-2023 12:48:50	08-06-2023 12:50:02
100289144	1	APPROVED	Jon Woo	08-06-2023 12:42:44	08-06-2023 12:50:02	08-06-2023 12:50:02
100289482	0	APPROVED	Alex Dallas	08-06-2023 11:38:44	08-06-2023 12:51:09	08-06-2023 12:53:02
100289482	0	APPROVED	Jon Woo	08-06-2023 11:38:44	08-06-2023 12:53:02	08-06-2023 12:53:02
100289483	0	APPROVED	Alex Dallas	08-06-2023 11:42:17	08-06-2023 12:53:43	08-06-2023 12:53:43

For the table above, is there a way to create a measure that would calculate the time difference between the response date and the next earliest response date for each quote number/version?

For instance, on the 3rd row, the measure would generate 1 minute and 16 seconds. For the 4th row, the measure would generate 1 minute and 56 seconds.

Also, for the second row, 'Alex Dallas' is the first approver on that quote number/quote version. The measure would need to calculate the time difference between the response date on that row and the submission date on that row.

Logic would be something like, “if row is earliest response date for that quote number/quote version, calculate time difference between response date and submission date, if else, calculate time difference between response date and next earliest response date.”

Any help would be greatly appreciated.

[ppm1 · Accepted solution]

This one might be good to pre-calculate as a column, if your logic never changes. Below is a DAX column expression that seems to work. Not sure how much data you have, but reply back if it is not performant and there may be a better way to write it. It returns the time in minutes as a decimal. It is best to keep durations as a decimal and format it later to mm:ss in your visuals if needed (you can't add up text strings, so not good to convert it yet). This could be adapted to be a measure but it may not be performant and whether it worked or not would depend on the fields used.

ResponseTime (minutes) =
VAR SubDT = Quotes[Submission Date]
VAR ThisResponse = Quotes[Response Date]
VAR ResponsesThisQuoteVersion =
    CALCULATETABLE (
        DISTINCT ( Quotes[Response Date] ),
        ALLEXCEPT ( Quotes, Quotes[Quote Number], Quotes[Quote Version] )
    )
VAR MinResponse =
    MINX ( ResponsesThisQuoteVersion, Quotes[Response Date] )
VAR PreviousResponse =
    MAXX (
        FILTER ( ResponsesThisQuoteVersion, Quotes[Response Date] < ThisResponse ),
        Quotes[Response Date]
    )
RETURN
    IF (
        MinResponse = ThisResponse,
        ThisResponse - SubDT,
        ThisResponse - PreviousResponse
    ) * 24 * 60

Calculate and Format Durations in DAX – Hoosier BI

Pat

[therightblue]

I wanted to thank you for your help on the referenced inquiry. It worked flawlessly.

I'm faced with another issue related to this inquiry. We have responses labeled 'Recalled'. This response can occur AFTER approval responses have taken place and can occur multiple times during the quote approval process. In the screenshot I provided below, you can see the RECALLED response occurs after two approvals.
Is there a way to filter the ‘CALCULATETABLE’ argument to only include ‘APPROVED’ responses after the Max RESPONSE DATE of ‘RECALLED’ response if a quote number/quote version contains a ‘RECALLED’ response?
Essentially, we’d be checking to see if a quote number/version contains a RECALLED response. If it does not, our existing DAX works as intended. If it does, we’d need our virtual table (CALCULATETABLE argument) to only include the ‘APPROVED’ responses after the max RESPONSE DATE of RECALLED response. Again, from there, our existing DAX logic would work because as you can see in the screenshot provided below, the submission date for the quote number/quote version reflects the timestamp of the latest submission (occurring after the last RECALLED response).

Quote Number	Quote Version	Response	Responder User Name	Submission Date	Response Date	Last Update Date
100298562	2	APPROVED	Sean Dunn	07-07-2023 14:50:33	07-06-2023 15:20:38	07-07-2023 17:15:54
100298562	2	APPROVED	Robert Sacre	07-07-2023 14:50:33	07-07-2023 11:56:46	07-07-2023 17:15:54
100298562	2	RECALLED	Alex Dallas	07-07-2023 14:50:33	07-07-2023 14:47:42	07-07-2023 17:15:54
100298562	2	APPROVED	Alex Dallas	07-07-2023 14:50:33	07-07-2023 14:51:05	07-07-2023 17:15:54
100298562	2	APPROVED	Robert Sacre	07-07-2023 14:50:33	07-07-2023 15:30:19	07-07-2023 17:15:54
100298562	2	APPROVED	John Birth	07-07-2023 14:50:33	07-07-2023 15:42:40	07-07-2023 17:15:54
100298562	2	APPROVED	Christopher Prescott	07-07-2023 14:50:33	07-07-2023 17:10:33	07-07-2023 17:15:54

[therightblue]

This seems to be working as intended. Thank you for your help. Currently, the dataset is small, but it is expected to grow signifactly in time. We do not currently have a finalized retention policy. Would you be able to provide the measure as an alternative?