Find first time customer/product sales

[Anonymous]

I am struggling to create a report to show first-time customer/product sales. The customers could be old or new, and the products could also be old or new, but we want to see when the first combination of the two occurs. 

I have a measure that shows the sales amount:

New Customers Sales = CALCULATE(
[Net Sales],
FILTER('Customer',[Net Sales]>0),
FILTER('Customer', CALCULATE([Net Sales]=0,FILTER(ALL('Date'),'Date'[Date] < MIN('Date'[Date])

))))

...and I've created similar measure for new product sales amount, but I can't figure out how to make a table showing the transactions.

Any advice?

[nickchobotar]

Hello,

One way to do this is by filtering the visual "table or matrix" directly with the measure. In this case my measure name is First Pruchase. All i have to day is select a table from the visualizaitions panel insert customer, productid, orderid and etc and finish with inserting the measure. It will filter your table down and show only the products your customers purchased for the first time.

First Pruchase =
VAR FirstTimePurchase =
    CALCULATE (
        MIN ( FlatFile[orderdate] ),
        ALLEXCEPT ( FlatFile, FlatFile[custid] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( FlatFile ),
        FILTER ( 
            FlatFile, 
            FlatFile[orderdate] <= FirstTimePurchase 
          )
    )

[Anonymous]

Thanks, but I must be doing something wrong, as it's not working for me...

[nickchobotar]

Hello @Anonymous 

I will have to look at your model to see why it's not working. Another option that will definitely work is a calculated column running on your fact table Document Sales. In this way you willl have a clean table with no addtional columns.

Here is the DAX .

**** Please use CustomerID from the Document Sales table not for the Customer Dimension

colFirstPurchaseDate = 
IF(
    Document Sales[Posting Date] = 
CALCULATE(
    MIN(Document Sales[Posting Date),
    FILTER(
        Document Sales,
        Document Sales[Posting Date] <=EARLIER(Document Sales[Posting Date)
            && Document Sales[CustomerID] <= EARLIER(Document Salses[CustomerID])
        
    )
), 1, 0)

You will have to add the newly created calcualted column to the Power BI table Visual level filters section and in the "Show items when the value:"  enter 1 and click "Apply filter" 

[hellind]

This is a common pattern that gets asked be management.

Show the current sales into a) New Clients b) Existing Clients Purchasing First-time Products c) Existing Clients Purchasing Same Products

It would be nice if there was some blog about this. But as of now i am still testing different approaches.

[v-qiuyu-msft]

Hi @Anonymous,

I created a report based on dummy data below:

We can create a measure like below:

Measure = var NewCustomer=CALCULATE(MIN('Table2'[Posting Date]),ALLEXCEPT(Table2,'Table2'[Customer]))
var NewProduct=CALCULATE(MIN('Table2'[Posting Date]),ALLEXCEPT(Table2,'Table2'[No]))
return
CALCULATE(COUNTROWS('Table2'),FILTER('Table2','Table2'[Posting Date]<=NewCustomer ||'Table2'[Posting Date]<=NewProduct))

Best Regards,
Qiuyun Yu

[OwenAuger]

Hi @Anonymous

I'm sure this can be done - just clarifying your requirements:

Do you want a measure that will return [Net Sales] only for sales where the particular combination of Customer/Product in each sale represents the first ever occurrence of that Customer/Product combination?

In other words, the measure will look at each sale's Date/Customer/Product, and if the Date is the earliest ever Date for that Customer/Product, return [Net Sales] otherwise return blank (and aggregate across all Customers/Products currently filtered).

Owen

[Anonymous]

Hi OwenAuger, and thanks for responding!

I am not loking for net sales, I have that already. What I want is a table that will display the transactional data for any sale that has either a new customer buying a new or existing product, and for any new product being sold to a new or existing customer. So it would be a filter of some sort I guess, applied to a table containing: date|order#|Customer|Item|Quantity|Amount

[OwenAuger]

I see, thanks.

So a Customer or Product is 'new' only in the very first transaction containing that Customer or Product?

This might be one of those cases where a calculated column (either DAX or Query Editor) works best.

I could create an example based on made-up data or do you want to post some sample data?

[Anonymous]

Yes, that's correct. The purpose is to show growth from customers buying something they hadn't in the past.