Forum Discussion

therightblue's avatar
therightblue
Frequent Visitor
3 years ago
Solved

Calculating Date/Timer Differences Dynamically

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 pr...
  • ppm1's avatar
    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 * 60

    Calculate and Format Durations in DAX – Hoosier BI

     

    Pat