Forum Discussion
Calculating Date/Timer Differences Dynamically
- 3 years ago
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 * 60Calculate and Format Durations in DAX – Hoosier BI
Pat
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 * 60Calculate and Format Durations in DAX – Hoosier BI
Pat
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?