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

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

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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'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]
    )
  • johnt75's avatar
    johnt75
    Super User
    4 years ago

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

      Thanks but I'm getting an error message 

       

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

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