Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

2nd last date

I have the following query below calculating the latest date per ID to give me an outcome, what i want to try and do is get the date previous to the max so the 2nd to last date is there an easy way i can amend the top VAR _maxdate to show 2nd last date rather than the max?

VAR _maxdate =
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[ID] = MAX ( 'Table1'[ID]) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table1'[outcome] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[ID] = MAX ( 'Table1'[ID])
                && 'Table1'[Date] = _maxdate
        )
    )

 

 

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    According to your measure, it seems that you want to show the outcome of the 2nd last Date for each ID ,right?

     

    I did it in two ways, please check.

     

    1. Get the last date firstly and then use MAXX() to get the date which is < the last date, it will be the 2nd last date:

    Measure1 = 
VAR _last =
    CALCULATE ( MAX ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) )
VAR _2nd =
    MAXX (
        FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] < _last ),
        [Date]
    )
RETURN
    CALCULATE (
        MAX ( 'Table1'[outcome] ),
        FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] = _2nd )
    )

    2.Use RANK() to sort by [Date] in descending order, then the 2nd last Date is [Rank]=2:

    Rank =
RANKX (
    FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) ),
    CALCULATE ( MAX ( ( 'Table1'[Date] ) ) ),
    ,
    DESC
)

    Measure2 = 
CALCULATE (
    MAX ( 'Table1'[outcome] ),
    FILTER ( ALL ( Table1 ), [ID] = MAX ( 'Table1'[ID] ) && [Rank] = 2 )
)

     Here is the final output:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Maybe:

    VAR _maxdate1 =
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[ID] = MAX ( 'Table1'[ID]) )
    )
VAR __maxdate = 
    CALCULATE (
        MAX ( 'Table1'[Date] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[ID] = MAX ( 'Table1'[ID]) && 'Table1'[Date]=__maxdate1 )
    )
RETURN
    CALCULATE (
        MAX ( 'Table1'[outcome] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[ID] = MAX ( 'Table1'[ID])
                && 'Table1'[Date] = _maxdate
        )
    )
  • DemoFour's avatar
    DemoFour
    Continued Contributor
    4 years ago

    Anonymous  When I have tackled something similar, I have used RANKEQ to rank the dates by ID and then found the max -1 that way.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    According to your measure, it seems that you want to show the outcome of the 2nd last Date for each ID ,right?

     

    I did it in two ways, please check.

     

    1. Get the last date firstly and then use MAXX() to get the date which is < the last date, it will be the 2nd last date:

    Measure1 = 
VAR _last =
    CALCULATE ( MAX ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) )
VAR _2nd =
    MAXX (
        FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] < _last ),
        [Date]
    )
RETURN
    CALCULATE (
        MAX ( 'Table1'[outcome] ),
        FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] = _2nd )
    )

    2.Use RANK() to sort by [Date] in descending order, then the 2nd last Date is [Rank]=2:

    Rank =
RANKX (
    FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) ),
    CALCULATE ( MAX ( ( 'Table1'[Date] ) ) ),
    ,
    DESC
)

    Measure2 = 
CALCULATE (
    MAX ( 'Table1'[outcome] ),
    FILTER ( ALL ( Table1 ), [ID] = MAX ( 'Table1'[ID] ) && [Rank] = 2 )
)

     Here is the final output:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.