Formula help with Summarize, filter, max date...

[MattAtBP]

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...

[Jihwan_Kim · Accepted 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] ) )
)

Link to the PBIX file 

[MattAtBP]

Many thanks, worked perfectly!

[Anonymous]

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

[MattAtBP]

Thanks Daxer for taking the time to help, works perfectly.

[Anonymous]

[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"
)