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

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's avatar
    MattAtBP
    Frequent Visitor
    5 years ago

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