Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alejandro_bi
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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

15 REPLIES 15
Zubair_Muhammad
Community Champion
Community Champion

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 )

Regards
Zubair

Please try my custom visuals

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

 

HI @Alejandro_bi

 

Did you add them as MEASURES?

 

See the attached file

 

 


Regards
Zubair

Please try my custom visuals

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

Thank you

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
    )

 


Regards
Zubair

Please try my custom visuals

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.

Hi @Alejandro_bi

 

I am having difficulty understanding the last requirement

 

Could you paste small dataset and result expected?


Regards
Zubair

Please try my custom visuals

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

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
    )

Regards
Zubair

Please try my custom visuals

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

 

Captura.PNG

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!

 

@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


Regards
Zubair

Please try my custom visuals

 

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

Regards
Zubair

Please try my custom visuals

It worked!

 

Thank you so much for your help!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.