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

    • Alejandro_bi's avatar
      Alejandro_bi
      Frequent Visitor
      8 years ago

      Here I attach the table that I have managed to obtain. It shows the client and when the last invoice was issued. To that table I would like to be able to add the amount of that invoice and the Id of the invoice.

      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

         

        Did you get it using the above method?

         

        If yes then you can modify it to include ID and Amount

         

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