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