Forum Discussion

MattAtBP's avatar
MattAtBP
Frequent Visitor
5 years ago
Solved

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

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 q...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

     

    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 

Anonymous's avatar
Anonymous
Not applicable
5 years ago
[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"
)