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 1...
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] )
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
Qty
johnt75 thanks, but when I add my full table it seems its only working if we are only looking at one column (product)
Anyway
What 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] )