Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] ) ) ) )
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 )
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
Did you add them as MEASURES?
I got it! But the last invoice issued appears, I would need the last invoice issued for each client. I explain?
Thank you
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 )
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.
I am having difficulty understanding the last requirement
Could you paste small dataset and result expected?
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.
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 )
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!
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
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] ) ) ) )
It worked!
Thank you so much for your help!
Yes, as you have indicated, but everything appears without data.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |