Measure to calculate new and previous values

[TimHoare]

I have some data that is of a format not disimilar to this (it is an oversimplification but what's important is that there is a list of records where 'CustomerName' - in this case - can appear multiple times):

CustomerName	Item	Amount	Date
Alex	Gloves	10	01/01/2020
Sarah	Shoes	20	02/01/2020
Steve	Shirt	15	03/01/2020
Steve	Shirt	15	04/01/2020
Alice	Hat	12	05/01/2020
Chris	Tie	6	05/01/2020
Chris	Shoes	20	06/01/2020
Tom	Shirt	15	05/01/2020
Tom	Shoes	20	06/01/2020
Tom	Socks	5	07/01/2020
Catherine	Hat	12	06/01/2020
John	Tie	6	08/01/2020
Tom	Socks	5	08/01/2020
Pete	Jacket	40	09/01/2020
Tom	Suit	80	09/01/2020
Catherine	Shirt	15	10/01/2020
Dave	Shirt	15	10/01/2020
Tom	Tie	6	10/01/2020
Tom	Trousers	25	11/01/2020
Chris	Socks	5	13/01/2020

An analysis I might like to do is: How many customers made at least one purchase on each day? I can do this by creating a measure 

TotalCustomers = DISTINCTCOUNT(transactions[CustomerName])

 and then plotting on a line chart by the date field.

I also want to know which customers made a purchase on a previous date and which ones are making their first. A way I have found to do this is to add a column at the database level that calculates the first date that a customer made a purchase: 

CustomerName	Item	Amount	Date	FirstPurchase
Alex	Gloves	10	01/01/2020	01/01/2020
Sarah	Shoes	20	02/01/2020	02/01/2020
Steve	Shirt	15	03/01/2020	03/01/2020
Steve	Shirt	15	04/01/2020	03/01/2020
Alice	Hat	12	05/01/2020	05/01/2020
Chris	Tie	6	05/01/2020	05/01/2020
Chris	Shoes	20	06/01/2020	05/01/2020
Tom	Shirt	15	05/01/2020	05/01/2020
Tom	Shoes	20	06/01/2020	05/01/2020
Tom	Socks	5	07/01/2020	05/01/2020
Catherine	Hat	12	06/01/2020	06/01/2020
John	Tie	6	08/01/2020	08/01/2020
Tom	Socks	5	08/01/2020	05/01/2020
Pete	Jacket	40	09/01/2020	09/01/2020
Tom	Suit	80	09/01/2020	05/01/2020
Catherine	Shirt	15	10/01/2020	06/01/2020
Dave	Shirt	15	10/01/2020	10/01/2020
Tom	Tie	6	10/01/2020	05/01/2020
Tom	Trousers	25	11/01/2020	05/01/2020
Chris	Socks	5	13/01/2020	05/01/2020

This way I can create another couple of measures:

NewCustomers =
CALCULATE (
    DISTINCTCOUNT ( transactions[CustomerName] ),
    FILTER ( transactions, transactions[Date] = transactions[FirstPurchase] )
)

PreviousCustomers = [TotalCustomers] - [NewCustomers]

and visualise this using something like a stacked area chart. 

My question is: This doesn't seem like an ideal way to do this, hardcoding these values doesn't seem right and maybe won't be as flexible if I want to breakdown by other dimensions - is there a way to achieve the same result using only measures (i.e. calculate first purchase date and then new and previous customers using DAX), or is this the best solution?

Thanks!

[v-kelly-msft · Accepted solution]

Hi @TimHoare ,

You need to create the following measures:

The first dayM =
CALCULATE (
    MIN ( 'transactions'[Date] ),
    ALLEXCEPT ( transactions, 'transactions'[CustomerName] )
)

Numbers of customers =
VAR a =
    SUMMARIZE ( 'transactions', 'transactions'[CustomerName], 'transactions'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( a ),
        ALLEXCEPT ( 'transactions', 'transactions'[Date] )
    )

Customer category =
VAR diff2 =
    SELECTEDVALUE ( 'transactions'[Date] ) - [The first dayM]
RETURN
    IF ( diff2 = 0, "the first time", "have purchased on a previous date" )

Finally you will see :

Also you can create calculated columns as below:

Firstday = CALCULATE(MIN('transactions'[Date]),ALLEXCEPT(transactions,'transactions'[CustomerName]))

Column = 
var diff='transactions'[Date]-'transactions'[Firstday]
Return
IF(diff=0,"the first time","have purchased on a previous date")

And you will see:

For the related .pbix file,pls click here.

Best Regards,

Kelly