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

 

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

     

    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

     

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

    With the method that I have to obtain the following table:

     

    As you can see, only the last invoices issued appear, that is, only the clients to whom the invoice was issued on the last day of issue. What I need is for each customer to have the last invoice that was issued and the date it was issued, along with the amount and id of the invoice. In the capture that I put the previous message appears each client and the date in which his last invoice was issued, but I have not managed to show the amount and id of the invoice.

     

    Thanks for your help!

     

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

    Alejandro_bi

     

    Ok.... I got you now

     

    So last date is specific to each Customer.

    I was using one single Last Date i.e. for the entire table

     

    Please could you share your file via onedrive or google drive

     

    Hopefully I will fix it in a while

  • Alejandro_bi's avatar
    Alejandro_bi
    Frequent Visitor
    8 years ago
  • Alejandro_bi's avatar
    Alejandro_bi
    Frequent Visitor
    8 years ago

    It worked!

     

    Thank you so much for your help!