Adding in a Condition that deals with quotes that feature a recall.

[Anonymous]

Hi everyone, 

I'm in need of some help... 

I'm trying to edit my DAX Measure seen below to add in a condition that would deal with quotes that feature a recall in its history. 

Currently, the Measure takes the submission date from the final line of the history, and subtracts that by the response date. 

Essentially, the condition would be able to detect if a quote has a recall in its history and would take the response date from the recalled line and take the place of the submission date in the final line subtraction. 

I have a screenshot attached below to hopefully provide a good visual example of what I would be looking to do. 

Below is the DAX Measure that I'd be looking to put the condition into:

Quote Turnaround Time for Approval Report Test File =

VAR ResponsesThisQuoteVersion =

    CALCULATETABLE (

        DISTINCT ( 'Test Approval Report'[Response Date] ),

        ALLEXCEPT ( 'Test Approval Report', 'Test Approval Report'[Quote Number], 'Test Approval Report'[Quote Version] )

    )

var ThisResponse = AVERAGEX('Test Approval Report','Test Approval Report'[Response Date])

var SubDT = AVERAGEX('Test Approval Report','Test Approval Report'[Submission Date ])

VAR MaxResponse =

    MAXX( ResponsesThisQuoteVersion, 'Test Approval Report'[Response Date] )

RETURN

    IF (

        ThisResponse <> MaxResponse,

        BLANK(),

        MaxResponse - SubDT

    ) *24 * 60

For what it's worth, I was also able to create calculated columns that would retrieve the previous row's value. Not sure if that's a relevant detail to provide or not. 

For whomever takes the time to read this and is able to provide suggestions/help, thank you!

[Anonymous]

Am currently testing this out, and it is working. Thank you so much! 

[johnt75 · Accepted solution]

Try

Quote Turnaround Time for Approval Report Test File =
VAR ResponsesThisQuoteVersion =
    CALCULATETABLE (
        DISTINCT ( 'Test Approval Report'[Response Date] ),
        ALLEXCEPT (
            'Test Approval Report',
            'Test Approval Report'[Quote Number],
            'Test Approval Report'[Quote Version]
        )
    )
VAR MaxRecall =
    CALCULATE (
        MAX ( 'Test Approval Report'[Response Date] ),
        ALLEXCEPT (
            'Test Approval Report',
            'Test Approval Report'[Quote Number],
            'Test Approval Report'[Quote Version]
        ),
        'Test Approval Report'[Response] = "RECALLED"
    )
VAR ThisResponse =
    AVERAGEX ( 'Test Approval Report', 'Test Approval Report'[Response Date] )
VAR SubDT =
    AVERAGEX ( 'Test Approval Report', 'Test Approval Report'[Submission Date ] )
VAR MaxResponse =
    MAXX ( ResponsesThisQuoteVersion, 'Test Approval Report'[Response Date] )
RETURN
    IF (
        ThisResponse <> MaxResponse,
        BLANK (),
        MaxResponse - COALESCE ( MaxRecall, SubDT )
    ) * 24 * 60