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 quite getting there. Below is some sample data and the desired result. 

 

Thanks for looking...

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 

5 Replies

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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