2nd last date

[Anonymous]

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-eqin-msft · Accepted solution]

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.

[DemoFour]

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

[Greg_Deckler]

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