cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to know the last invoice amount

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 15
Super User

## Re: How to know the last invoice amount

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 )```
Frequent Visitor

## Re: How to know the last invoice amount

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

Super User

## Re: How to know the last invoice amount

Did you add them as MEASURES?

See the attached file

Frequent Visitor

## Re: How to know the last invoice amount

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

Frequent Visitor

## Re: How to know the last invoice amount

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

Thank you

Super User

## Re: How to know the last invoice amount

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
)```

Frequent Visitor

## Re: How to know the last invoice amount

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.

Super User

## Re: How to know the last invoice amount

I am having difficulty understanding the last requirement

Could you paste small dataset and result expected?

Frequent Visitor

## Re: How to know the last invoice amount

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.