Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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] )
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |