find value at calculated date

[ChrisPfP]

I need to find a value based on a calculated date - ie what was [NewValue] on the first instance it was one of "Put it Right", "Stage 1" , "Stage 2" or "Stage 3". This is the measure I'm using to find the date but I can't work out how to show the [NewValue] (it's easy in SQL 

:

FirstStageDate =

var current_row_ParentId = min('History: Complaint'[ParentId])
var current_row_NewValue = min('History: Complaint'[NewValue])

var earliest_stage =
CALCULATE(
min('History: Complaint'[CreatedDate]),
FILTER(
ALLEXCEPT('History: Complaint','History: Complaint'[ParentId]),
'History: Complaint'[NewValue] = "Put it Right" || 'History: Complaint'[NewValue] = "Stage 1" || 'History: Complaint'[NewValue] = "Stage 2" || 'History: Complaint'[NewValue] = "Stage 3"
)
) return

earliest_stage

[johnt75]

You can try

Value at earliest stage =
VAR summaryTable =
    TOPN (
        1,
        FILTER (
            ALLEXCEPT ( 'History: Complaint', 'History: Complaint'[ParentId] ),
            TREATAS (
                { "Put it Right", "Stage 1", "Stage 2", "Stage 3" },
                'History: Complaint'[NewValue]
            )
        ),
        'History: Complaint'[CreatedDate]
    )
RETURN
    SELECTCOLUMNS ( summaryTable, [NewValue] )

[ChrisPfP]

Thanks but I'm getting an error message 

[johnt75]

What's the definition of Account[Case Owner] ?

[ChrisPfP]

Sorry I have no idea how that field got into the DAX!

[tamerj1]

Hi @ChrisPfP 
Please try

FirstStageDate =
CALCULATE (
    MIN ( 'History: Complaint'[CreatedDate] ),
    ALLEXCEPT ( 'History: Complaint', 'History: Complaint'[ParentId] ),
    FILTER (
        'History: Complaint',
        'History: Complaint'[NewValue] = "Put it Right"
            || 'History: Complaint'[NewValue] = "Stage 1"
            || 'History: Complaint'[NewValue] = "Stage 2"
            || 'History: Complaint'[NewValue] = "Stage 3"
    )
)

[ChrisPfP]

Thanks but this isn't showing me the [NewValue] value that I need to see

[tamerj1 · Accepted solution]

@ChrisPfP 

Please try

FirstStageDate =
VAR CurrentIDTable =
    CALCULATETABLE (
        'History: Complaint',
        ALLEXCEPT ( 'History: Complaint', 'History: Complaint'[ParentId] )
    )
VAR FilteredTable =
    FILTER (
        CurrentIDTable,
        'History: Complaint'[NewValue] = "Put it Right"
            || 'History: Complaint'[NewValue] = "Stage 1"
            || 'History: Complaint'[NewValue] = "Stage 2"
            || 'History: Complaint'[NewValue] = "Stage 3"
    )
VAR MinDate =
    MINX ( FilteredTable, 'History: Complaint'[CreatedDate] )
RETURN
    MAXX (
        FILTER ( FilteredTable, 'History: Complaint'[CreatedDate] = MinDate ),
        'History: Complaint'[Value]
    )