Second Last Invoice Amount

[shanipowerbi]

Hi Experts

I need your help to calculate second last invoice amount. I have two table that contain

1 - Invoive Data

Invoice ID / User ID / Invoice Amount / Invoice Date

2 - User Profiling Data

User ID / Channel / Conversion Date / Signup Date

I have made relationship and calcualte the last invoice amount by using 

Last Invoice Amount = Calculate (Sumx(Invoice Amount),LastDate(Invoice Date)

this formula gave me the last invoice amount of each user in User Profiling Data, now I am stuck in Second last invoice amount and I need it badly to calculate some very important analysis. Please look into this 

[Anonymous]

is the invoice date different for different users?

try the below post which is similar situation

https://community.powerbi.com/t5/Desktop/Min-Max-Date-and-finding-the-2nd-Latest-Date/m-p/55635#M225...

[VasTg]

@shanipowerbi 

Are you trying to create a new column in User_Profile table or is it gonna be a measure?

What is the relationship type between the tables?

How about sharing sample dataset?

[shanipowerbi]

@VasTg 

Yes trying to get new column, about relationship i have created relationship b/w User IDs

[VasTg · Accepted solution]

@shanipowerbi 

I believe you are trying to create a column.

This should work.

2ND Last Invoice Amount =
VAR LAST_DATE =
    LASTDATE ( Invoice[Invoice Date] )
VAR PREVIOUS_DATE =
    CALCULATE (
        MAX ( Invoice[Invoice Date] ),
        FILTER ( ALL ( Invoice[Invoice Date] ), Invoice[Invoice Date] < LAST_DATE )
    )
RETURN
    CALCULATE (
        SUM ( Invoice[Invoice Amt] ),
        Invoice[Invoice Date] = PREVIOUS_DATE
    )

If it helps mark it as a solution.

Kudos are nice too

[shanipowerbi]

@VasTg 

Your formula helped me. Now Issue is resolved

Regards