Forum Discussion

ChrisPfP's avatar
ChrisPfP
Regular Visitor
4 years ago
Solved

find value at calculated date

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 fin...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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]
    )
ChrisPfP's avatar
ChrisPfP
Regular Visitor
4 years ago

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

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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]
    )