Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |