topic Re: Formula help with Summarize, filter, max date... in DAX Commands and Tips

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

MattAtBP — Wed, 21 Jul 2021 15:31:16 GMT

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

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

Anonymous — Wed, 21 Jul 2021 17:18:54 GMT

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

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

Anonymous — Wed, 21 Jul 2021 17:22:33 GMT

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

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

Jihwan_Kim — Wed, 21 Jul 2021 17:36:05 GMT

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

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

MattAtBP — Thu, 22 Jul 2021 07:20:30 GMT

Many thanks, worked perfectly!

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

MattAtBP — Thu, 22 Jul 2021 07:21:48 GMT

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