Second Last Value

[Anonymous]

Hello

I have successfully produced the last value using this calculation:

Last Value =

SUMX(FILTER('Market Prices','Market Prices'[Date]=LASTDATE('Market Prices'[Date])),'Market Prices'[Value])

Which gets used by:

Last Trans =

VAR Last_Date = Lastdate('Market Prices'[Date])

RETURN

CALCULATE([Last Value],

    Filter(All('Market Prices'[Date]),

    'Market Prices'[Date] = Last_Date))

But I am having trouble getting second last transaction (Transaction before last transaction).  

[Anonymous]

Cheers, that looks great.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Here I suggest you to try below codes to create [Last Trans] and [Second Last Trans] measures.

Second Last Trans =
VAR _Last_Date =
    MAXX (
        FILTER (
            ALL ( 'Market Prices' ),
            'Market Prices'[Symbol] = MAX ( 'Market Prices'[Symbol] )
                && 'Market Prices'[Date] < MAX ( 'Market Prices'[Date] )
        ),
        'Market Prices'[Date]
    )
RETURN
    CALCULATE (
        SUM ( 'Market Prices'[Value] ),
        FILTER ( 'Market Prices', 'Market Prices'[Date] = _Last_Date )
    )

Last Trans = 
VAR _Last_Date =
    MAXX (
        FILTER (
            ALL ( 'Market Prices' ),
            'Market Prices'[Symbol] = MAX ( 'Market Prices'[Symbol] )
        ),
        'Market Prices'[Date]
    )
RETURN
    CALCULATE (
        SUM ( 'Market Prices'[Value] ),
        FILTER ( 'Market Prices', 'Market Prices'[Date] = _Last_Date )
    )

My Sample:

Result is as below.

Best Regards,
Rico Zhou

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