- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Last invoice amount
HI,
Problem: How can I calculate last invoice amount?
Simplified Model:
Customer Table with the following column:
CustomerID (Thera are only unique customers' id)
Sales Table with the following columns:
CustomerID | InvoiceNo | ProductName | ProductPrice | ProductQuantity| InvoiceAmount | InvoiceDate
Customer Tabel is conected to Sales Table with 1-to-many relations.
Measure:
#LastOrderDate = LASTDATE(Sales[Date])
My tabel report has the following columns:
Customer[CustomerID] | #LastOrderDate |
I would like to add 1 more measure to the table report to calculate LastOrderValue.
Thx for support!
Paweł
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What you want is an application of what we call "Last Non Empty" order. There are 4 types of Last Non Empty patterns, and if you want to dive deep into it, I would recommend you to one of my old blogs -
http://sqljason.com/2012/06/lastnonempty-in-tabular-mode-part-1.html
If you just want the solution, my understanding is that you want the last non empty order for all customers on that date.
Sales_LNE := CALCULATE ( SUM ( Sales[Sales] ), LASTDATE ( Sales[Date] ) )
This is the first pattern from my blog (and you can also use a filter() instead of the lastdate()). If the last date is different for every customer (which means, if you dont get orders for all customers on each date), you might want to use the second pattern with SUMX.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What you want is an application of what we call "Last Non Empty" order. There are 4 types of Last Non Empty patterns, and if you want to dive deep into it, I would recommend you to one of my old blogs -
http://sqljason.com/2012/06/lastnonempty-in-tabular-mode-part-1.html
If you just want the solution, my understanding is that you want the last non empty order for all customers on that date.
Sales_LNE := CALCULATE ( SUM ( Sales[Sales] ), LASTDATE ( Sales[Date] ) )
This is the first pattern from my blog (and you can also use a filter() instead of the lastdate()). If the last date is different for every customer (which means, if you dont get orders for all customers on each date), you might want to use the second pattern with SUMX.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried to expolore the LastEverNonEmpty measure for each customer as well but I am not sure if I can apply it to my case.

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
10-30-2024 09:24 PM | |||
02-19-2024 08:00 AM | |||
Anonymous
| 01-19-2024 10:26 AM | ||
02-26-2025 06:39 PM | |||
11-04-2024 04:27 PM |
User | Count |
---|---|
81 | |
78 | |
51 | |
38 | |
34 |
User | Count |
---|---|
92 | |
75 | |
53 | |
52 | |
45 |