How to know the last invoice amount

[Alejandro_bi]

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.

[Zubair_Muhammad]

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 )

[Alejandro_bi]

Sorry, I've tried it but all fields are blank. When you use VAR or Return, what is it for? I'm a bit of a rookie in all this.

Thanx

[Zubair_Muhammad]

HI @Alejandro_bi

Did you add them as MEASURES?

See the attached file

[Alejandro_bi]

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]

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]

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]

Hi @Alejandro_bi

I am having difficulty understanding the last requirement

Could you paste small dataset and result expected?

[Alejandro_bi]

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]

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]

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]

@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

[Zubair_Muhammad · Accepted solution]

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]

It worked!

Thank you so much for your help!

[Alejandro_bi]

Yes, as you have indicated, but everything appears without data.