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 issue...
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] )
        )
    )
)
Alejandro_bi's avatar
Alejandro_bi
Frequent Visitor
8 years ago

I got it! But the last invoice issued appears, I would need the last invoice issued for each client. I explain?

Thank you

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

    Hi Alejandro_bi

     

    Lets do it this way. Assuming your Table name is Table1

     

    Go to Modelling Tab.... Select the NEW TABLE button and enter this formula.

     

     

    NEW Table =
VAR Last_Date =
    LASTDATE ( ALL ( Table1[Date] ) )
RETURN
    FILTER (
        SUMMARIZE ( Table1, Table1[Client], Table1[Date], Table1[Amount] ),
        Table1[Date] = Last_Date
    )

     

    • Alejandro_bi's avatar
      Alejandro_bi
      Frequent Visitor
      8 years ago

      He created the table as I indicated, but only the last invoice that was issued still appears. I would need a list where all the clients appear with the date of the last invoice and its amount.

       

      Thank you very much for your help.

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

        Hi Alejandro_bi

         

        I am having difficulty understanding the last requirement

         

        Could you paste small dataset and result expected?