Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I'm trying to create a new table that shows the cost of the most recent purchase of 'Product 1', per customer. I've tried a formula using summarize with filter and max date but just not quite getting there. Below is some sample data and the desired result.
Thanks for looking...
Solved! Go to Solution.
New Table =
ADDCOLUMNS (
TREATAS (
GROUPBY (
FILTER ( Data, Data[product] = 1 ),
Customers[customer],
Products[product],
"@datemax", MAXX ( CURRENTGROUP (), Data[date] )
),
Data[customer],
Data[product],
Data[date]
),
"@cost", CALCULATE ( SUM ( Data[cost] ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
New Table =
ADDCOLUMNS (
TREATAS (
GROUPBY (
FILTER ( Data, Data[product] = 1 ),
Customers[customer],
Products[product],
"@datemax", MAXX ( CURRENTGROUP (), Data[date] )
),
Data[customer],
Data[product],
Data[date]
),
"@cost", CALCULATE ( SUM ( Data[cost] ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Many thanks, worked perfectly!
Another way of calculation:
[Your table] = // calulated table
CALCULATETABLE(
var Filter_ =
ADDCOLUMNS(
DISTINCT( T[Customer] ),
"Date", CALCULATE( MAX( T[Date] ) )
)
var Result =
CALCULATETABLE(
T,
TREATAS(
Filter_,
T[Customer],
T[Date]
)
)
return
Result,
T[Product] = "product 1"
)
Thanks Daxer for taking the time to help, works perfectly.
[Your table] = // calulated table
CALCULATETABLE(
GENERATE(
SUMMARIZE(
T,
T[Customer],
T[Product],
),
SELECTCOLUMNS(
CALCULATETABLE(
TOPN(1,
T,
T[Date],
DESC
)
),
"Date", T[Date],
"Cost", T[Cost]
)
),
T[Product] = "product 1"
)
User | Count |
---|---|
66 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |