Forum Discussion
Solved
Last QTY based on selected date
Hello would it be okay if you can help me on this? I got stucked
So I have this table
| Product | Timestamp | Qty |
| A | 1-Jan | 23 |
| A | 2-Jan | 123 |
| A | 3-Jan | 123 |
| A | 4-Jan | 213 |
| B | 1-Jan | 123 |
| B | 2-Jan | 123 |
| B | 4-Jan | 123 |
| C | 1-Jan | 10 |
| C | 2-Jan | 11 |
| C | 4-Jan | 13 |
and the result that im looking for is this
Filter Timestamp = 1/3
| Product | Last Qty |
| A | 123 |
| B | 123 |
| C | 11 |
Explanation : So what I'm looking for is I want to get the latest qty by Product depending on what I filter.
So If I filter 1/3/2023 it will only scan below or equal to that date.
A = 123 from 1/3/2023
B = 123 from 1/2/2023
C = 11 from 1/2/2023
Try
Qty as at date = VAR MaxDate = MAX ( 'Date'[Date] ) VAR SummaryTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Product], 'Table'[Subtype] ), "@qty", SELECTCOLUMNS ( CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[Timestamp], DESC ), 'Date'[Date] <= MaxDate ), "@qty", 'Table'[Qty] ) ) RETURN SUMX ( SummaryTable, [@qty] )
3 Replies
You can create a measure like
Qty as at date = VAR MaxDate = MAX ( 'Date'[Date] ) VAR Qty = SELECTCOLUMNS ( CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[Timestamp], DESC ), 'Date'[Date] <= MaxDate ), "@qty", 'Table'[Qty] ) RETURN Qtyjohnt75 thanks, but when I add my full table it seems its only working if we are only looking at one column (product)
AnywayWhat if this is the table, I added a subtype
Product Timestamp Subtype Qty A 1-Jan A-1 23 A 2-Jan A-1 123 A 3-Jan A-2 123 A 4-Jan A-2 213 B 1-Jan B-1 123 B 2-Jan B-1 123 B 4-Jan B-2 123 C 1-Jan C-1 10 C 2-Jan C-1 11 C 4-Jan C-3 13 This is the error I got.
The result im looking for is
Product Qty explanation A 246 A-1 (jan 2) = 123 A-2 (jan3) = 123 Total = 246 B 123 B-1(jan 2) = 123 Total = 123 C 11 C-1(jan2) = 11 Total = 11
Let me know if it makes sense.Try
Qty as at date = VAR MaxDate = MAX ( 'Date'[Date] ) VAR SummaryTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Product], 'Table'[Subtype] ), "@qty", SELECTCOLUMNS ( CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[Timestamp], DESC ), 'Date'[Date] <= MaxDate ), "@qty", 'Table'[Qty] ) ) RETURN SUMX ( SummaryTable, [@qty] )
