cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alejandro_bi Frequent Visitor
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
Super User

Re: How to know the last invoice amount

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] )
        )
    )
)
15 REPLIES 15
Super User
Super User

Re: How to know the last invoice amount

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 Frequent Visitor
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
Super User

Re: How to know the last invoice amount

HI @Alejandro_bi

 

Did you add them as MEASURES?

 

See the attached file

 

 

Alejandro_bi Frequent Visitor
Frequent Visitor

Re: How to know the last invoice amount

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

Alejandro_bi Frequent Visitor
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
Super User

Re: How to know the last invoice amount

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 Frequent Visitor
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
Super User

Re: How to know the last invoice amount

Hi @Alejandro_bi

 

I am having difficulty understanding the last requirement

 

Could you paste small dataset and result expected?

Alejandro_bi Frequent Visitor
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.

 

Captura.PNG