Data and data model
NEW CUSTOMER
I want to create a measure that will display the first purchase for each customer. That is the logic behind a definition of a new customer
Customers Count =
DISTINCTCOUNT(DimCustomers[CUSTOMERID])
step 1
First purchase by customer = CALCULATE(FIRSTDATE(FactSales[DATE]), REMOVEFILTERS(DimDate))
REMOVEFILTERS(DimDate)) works similarly as ALL(). This function will return the first purchase date from the FactSales table for each row in table ignoring the filter coming from DimDate table.
step 2
Create a virtual table, that contains the dates of the first purchase for each customer.
HelpTbl First Purchase date by customer =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"FISTPURCHASEDATE", [First purchase by customer]
),
REMOVEFILTERS ( FactSales ),
REMOVEFILTERS ( DimDate ))
step 3
Create a virtual bridge table.
HelpTbl First Purchase date by customer =
VAR BaseTable =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"FISTPURCHASEDATE", [First purchase by customer]
),
REMOVEFILTERS ( FactSales ),
REMOVEFILTERS ( DimDate )
)
VAR BridgeTable =
TREATAS (BaseTable, DimCustomers[CUSTOMERID], DimDate[Date] )
RETURN
BridgeTable
Both tables BaseTable and BridgeTable return the same result. CustomerID and Date, which is the First purchase by customer. Problem is that I can not use the BaseTable in my final calculation, which suppose to return the distinct count of the new customers. So I will copy the data from the BaseTable into the BridgeTable.
step 4
I will use the previously prepared virtual tables to create a measure that will display the first purchase for each customer.
New Customer =
VAR BaseTable =
CALCULATETABLE (ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"FISTPURCHASEDATE", [First purchase by customer]
),
REMOVEFILTERS ( FactSales ),
REMOVEFILTERS ( DimDate )
)
VAR BridgeTable =
TREATAS (BaseTable, DimCustomers[CUSTOMERID], DimDate[Date] )
VAR Result =
CALCULATE (DISTINCTCOUNT ( FactSales[CUSTOMERID] ),
KEEPFILTERS (BridgeTable))
RETURN Result
RETURNING CUSTOMER
step 1
Create a virtual table, that contains the date of the first purchase for each customer.
RETURNING CUSTOMERS =
VAR MinDate = MIN ( 'DimDate'[Date] )
VAR NewCustomer =
CALCULATETABLE (ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"CustomerFirstPurchase", [First purchase by customer]
),
ALLSELECTED ( DimCustomers ),
ALLSELECTED ( DimDate )
)
RETURN NewCustomer
step 2
create a variable ( table) that checks whether their first purchase was made before the beginning of the current period.
First purchase by customer = CALCULATE(FIRSTDATE(FactSales[DATE]), REMOVEFILTERS(DimDate))
step 3 Adding a variable (table) ReturningCustomers. This is an intersection between all customers and customers that have purchased more than once.
Step 4
Create a variable , use COUNTROWS() function, to calculate the number of rows where the intersection happened. Return the result. JOHN01 is the only one returning customer. The rest of the customers are new customers.
If you return ReturningCustomer variable you will get the names of the returning customers instead of its count.
Returning Customers =
VAR MinDate = MIN ( 'DimDate'[Date] )
VAR NewCustomer =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"CustomerFirstPurchase", [First purchase by customer]
),
ALLSELECTED ( DimCustomers ), ALLSELECTED ( DimDate )
)
VAR ExistingCustomers =
FILTER (NewCustomer,
[First purchase by customer] < MinDate
)
VAR ReturningCustomers =
INTERSECT (VALUES ( FactSales[CUSTOMERID] ),
SELECTCOLUMNS (ExistingCustomers,
"CustomerID", FactSales[CUSTOMERID]
))
VAR Result =COUNTROWS (ReturningCustomers)
RETURN ReturningCustomers
LOST CUSTOMER
SALES TABLE
WHO'S LOST CUSTOMER ? As you can see from this picture I have only 14 distinct transaction dates. The last date is on 14/01/2021, so the logic behind the lost customer will be something like a : every customer who haven't got any sale 3 days after his last transaction will be considered as a lost customer.
step 1
CUSTOMER LOSS DATE =
CALCULATE (
MAX ( FactSales[DATE] ) + 3,
REMOVEFILTERS ( 'DimDate' ))
According this logic, Marek01 who had the last transaction on 11/01/21 will be considered as a lost customer on 14/01/21.
In case that you have a larger data set, you can try to use a different logic. For example a customer will be lost six months after the last transaction date.
The last sale occurs six months after the last transaction at any time
CUSTOMER LOSS DATE (+6months) =
CALCULATE (
EOMONTH (MAX ( FactSales[DATE] ), 6),
REMOVEFILTERS ( 'DimDate' ))
step 2
This is the first part of the measure.
LastTransactionDate variable returns the last date from FactSale table. DimDate and FactSale contain the same dates. (Start / End) .
LostCustomerDate variable returns the CustomerID and a date column which holds the dates, when a customer is considered to be lost. For example JOHN01 will be considered as a lost customer on 17.Jan.2021. The last date in the FactSale table is 14.01.2021 which is the current date or one date before the current date. So JOHN01 won't be considered as a lost customer in the current content.
step 3
As I have said in the previous step JOHN01 is not a lost customer in the current concept. He just will be consider as a lost customer without any further sales .
LostCustomer variable determines the lost customer within the current time period.
step 4
The final measure
Lost Customers =
VAR LastTransactionDate =CALCULATE (MAX ( 'DimDate'[Date] ),
ALLSELECTED ( 'DimDate' ))
VAR CustomersByLastTransaction =
CALCULATETABLE (ADDCOLUMNS
(VALUES(FactSales[CUSTOMERID]),
"_LostCustomerDate", [CUSTOMER LOSS DATE]),
ALLSELECTED ( DimCustomers ),
'DimDate'[Date] <= LastTransactionDate)
VAR LostCustomers = FILTER (CustomersByLastTransaction,
[_LostCustomerDate]
IN VALUES ( 'DimDate'[Date] ))
VAR Result = COUNTROWS (LostCustomers)
RETURN Result
Sales New Customers
This measure will show the sum of the sales for those customers which are considered to be the new customers only.
step1
First purchase by customer =
CALCULATE (MIN ( FactSales[DATE] ), REMOVEFILTERS ( DimDate ))
The date of the first sale is the MIN of FactSales[date] at any time in the past.
step 2
First part of the measure is a list of the customers together with their first purchase date.
step 3
NewCustomers variable retrieves from CustomersFirstPurchase variable just the customers, where the new customer date is in the current time period.
step 4
the final measure
$585 is the total sale for each day. = Sum(FactSales[SALES]
Sales New Customers =
VAR CustomersFirstPurchase =
CALCULATETABLE (ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"_NewCustomerDate", [First purchase by customer]),
ALLSELECTED ( DimCustomers ),
ALLSELECTED ( 'DimDate' ))
VAR NewCustomers =
FILTER (CustomersFirstPurchase,
[_NewCustomerDate]
IN VALUES ( 'DimDate'[Date] ))
VAR Result =
CALCULATE (SUM ( FactSales[SALES] ),
KEEPFILTERS (NewCustomers))
RETURN Result
Sales Returning Customers
step 1
Prepare a table that contains the first purchase data for each customer regardless of the local filters on DimCustomers and DimDate.
step 2
ExistingCustomer variable works as a filter for the ReturningCustomers variable.
ExistingCustomer filters all customers and checks that their first purchase took place befor the start of the current period.
ReturningCustomers variable obtains the returning customers as the intersection between the active customers in the selection and the existing customers.
step 3
Sales by the returning customers measure. I have just one returning customer: JOHN01. The rest of them are new customers.
This measure shows the sum of the sales for those customers which are considered to be the returning customers only.
Sales By Returning Customers =
VAR _FistDate =FIRSTDATE ( 'DimDate'[Date] )
VAR CustomersFirstPurchase =CALCULATETABLE (ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"_NewCustomerDate", [First purchase by customer]
),
REMOVEFILTERS ( DimCustomers ),
REMOVEFILTERS ( 'DimDate' )
)
VAR ExistingCustomers = FILTER (CustomersFirstPurchase,
[_NewCustomerDate] < _FistDate
VAR ReturningCustomers = INTERSECT (
VALUES ( FactSales[CUSTOMERID] ),
SELECTCOLUMNS (ExistingCustomers,
"CustomerID", FactSales[CUSTOMERID]
))
VAR Result = CALCULATE ([Sales Amount],
KEEPFILTERS (ReturningCustomers)
)
RETURN Result
Sales Lost Customers
step1
The definition of the lost customer whiting my data set which contains just 14 days of data is that a lost customer is someone whose last sale occurs 3 days after his last transaction . If he hasn't got any sale for 3 days he is a lost customer.
CUSTOMER LOSS DATE = CALCULATE ( MAX ( FactSales[DATE] )+3 , REMOVEFILTERS ( 'DimDate' ) )
Another definition for a lost customer can be: This person hasn't got any sale for 2 months from his last sale transaction.
CUSTOMER LOSS DATE (+2 months after last sale) = CALCULATE ( EOMONTH ( MAX ( FactSales[DATE] ), 2 ), REMOVEFILTERS ( 'DimDate' ) )
step 2
Prepare a table that for each customer contains the date when they are considered lost.
step 2
LostCustomers and Previous14Days table variables are needed as the filters for the sale measure return on the end.
LostCustomers retrieves just the customers who are considered lost in the current filter context or time period. Previous14Days : this is the period for which I want to see the lost customers.
If your lost customer is someone who hasn't got any sale for 2 months and you would like to have a look on the whole last year instead of just 14 days, you can replace the variable with this one:
VAR Previous12Months(Retrieve the previous 12 months) = DATESINPERIOD ( 'DimDate'[Date],
EOMONTH ( MAX ( 'DimDate'[Date] ), -2 ), -12, MONTH)
step 3
Calculating the sales amount just for the lost customers
Sales by Lost Customers (in the last 14-DAYS period) =
VAR LastDateLost =
CALCULATE (MAX ( 'DimDate'[Date] ), ALLSELECTED ( 'DimDate' ))
VAR LostCustomersTable =
CALCULATETABLE (ADDCOLUMNS (
VALUES ( FactSales[CUSTOMERID] ),
"_LostCustomerDate", [CUSTOMER LOSS DATE]),
ALLSELECTED ( DimCustomers ),
'DimDate'[Date] <= LastDateLost
)
VAR LostCustomers =
FILTER (LostCustomersTable, [_LostCustomerDate]
INVALUES ( 'DimDate'[Date] ))
VAR Previous14Days =
DATESINPERIOD ('DimDate'[Date],
MAX ( 'DimDate'[Date] ) - 3,14,DAY)
VAR Result =
CALCULATE ( [Sales Amount], Previous14Days, KEEPFILTERS (LostCustomers))
RETURN Result
