Forum Discussion

Alejandro_bi's avatar
Alejandro_bi
Frequent Visitor
8 years ago
Solved

How to know the last invoice amount

Hello, in a table I have the date of issuance of an invoice, the amount of said invoice and the client to whom it corresponds. I would like to know when it was the last date that an invoice was issued to the client and its amount. Someone help me?

Thank you.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Alejandro_bi

     

    Use this formula.

     

    Hopefully it will get you the results

     

    NEW Table =
SUMMARIZE (
    Table1,
    Table1[Client],
    "Last Date", LASTDATE ( Table1[Date] ),
    "Last Amount", CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Client] ),
            Table1[Date] = MAX ( Table1[Date] )
        )
    ),
    "Last Invoice ID", CALCULATE (
        VALUES ( Table1[Invoice ID] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Client] ),
            Table1[Date] = MAX ( Table1[Date] )
        )
    )
)

15 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    HI Alejandro_bi

     

    Try these MEASURES

     

    LastDate =
LASTDATE ( ALL ( Table1[Date] ) )
    Client_LastDate =
VAR Last_Date =
    LASTDATE ( ALL ( Table1[Date] ) )
RETURN
    CALCULATE ( VALUES ( Table1[Client] ), Table1[Date] = Last_Date )
    Amount_LastDate =
VAR Last_Date =
    LASTDATE ( ALL ( Table1[Date] ) )
RETURN
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Date] = Last_Date )