Need a table showing only Customers and Last Sale Date & Amount

[Roseventura]

What's the best way to create a virtual table that pulls the Customer Code, the Last Sale Date and Total Sales for that customer?

The goal is to generate a list of Customers who have not purchased from us in the last year.

Here's is some sample data:

I need to produce a table in Power BI exactly like the Pivot table shown above.  My SUMMARIZECOLUMNS measure produces the result similar to what is shown on the left.  I need to just see the Last Sale Date and the total sales for each customer (highlighted rows are what is shown in the pivot table).

I tried using SUMMARIZECOLUMNS, but that didn't work.  I tried CALCULATETABLE, but I get an error.

Here is my SUMMARIZECOLUMN measure:

Summarize Lost Customers =

SUMMARIZECOLUMNS(

Shipments[CUSTOMER CODE],

Shipments[Date],

filter(Shipments, Shipments[Date] < TODAY() - 364 ) ,

"Lost Sales", [Sales] )

[lbendlin]

Something like this should work:

SUMMARIZE(Shipments,Shipments[CUSTOMER CODE],"Last Sale",MAX(Shipments[Date]),"Total Sales",SUM([Sales]))

[Roseventura]

Ibendlin:

I tried that a few days ago, but I need to incorporate the filter shown in BOLD below (which I why I switched from SUMMARIZE to SUMMARIZECOLUMNS.

Summarize Lost Customers =

SUMMARIZECOLUMNS(

Shipments[CUSTOMER CODE],

Shipments[Date],

filter(Shipments, Shipments[Date] < TODAY() - 364 ) ,

"Lost Sales", [Sales] )

Also, that 364 in the measure above needs to be a variable which is selected by the user, a Churn Time Period Value which the user selects from a range from 0 to 390 days.

The end result I need is to generate a table of Customers who have NOT purchased from us in over a year or whichever time period the user selects.  Your suggestion summarizes ALL customers regardless of when they purchased.

[v-rzhou-msft · Accepted solution]

Hi @Roseventura 

I  think you want to show dynamic result by days slicer. Power BI doesn't support calculated table to show dynamic result based on slicer. I suggest you to create measures. I think you want to calculate the max day before today()-N and sum of Sales before today()-N.

Measures:

MAX of DATE = 
VAR _SELECTDAYS = SELECTEDVALUE(Days[Days])
RETURN
CALCULATE(MAX(Shipments[DATE]),FILTER(Shipments,Shipments[DATE]< TODAY()-_SELECTDAYS))

Sum of SALES = 
VAR _SELECTDAYS = SELECTEDVALUE(Days[Days])
RETURN
CALCULATE(SUM(Shipments[SALES]),FILTER(Shipments,Shipments[DATE]< TODAY()-_SELECTDAYS))

Result:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.