topic Re: New Customer count and sales calculation in DAX Commands and Tips

New Customer count and sales calculation

mbacicci — Tue, 12 Dec 2023 12:56:06 GMT

Hi, could you help me with this measure?
I'm not sure about the below calculations I've created to return count of absolute new customers in a table. The measure should look at all available data but return only new customers for specific year.

(Moreover, the totals don't add up to neither of the measures)

Thank you!

New Customers Year =

VAR currentCustomers = VALUES('_Combined'[Customer name])

VAR currentDate = MAX('*DATE TABLE'[Date])

VAR prevYear = YEAR(currentDate) - 1

VAR pastCustomers =

CALCULATETABLE(

VALUES('_Combined'[Customer name]),

ALL('_Combined'),

YEAR('*DATE TABLE'[Date]) = prevYear,

'*DATE TABLE'[Date] < DATE(YEAR(currentDate), 1, 1)

)

VAR newCustomers = EXCEPT(currentCustomers, pastCustomers)

RETURN

COUNTROWS(newCustomers)

-----

New Customers Sales =

VAR currentCustomers = VALUES('_Combined'[Customer name])

VAR currentDate = MAX('*DATE TABLE'[Date])

VAR pastCustomers = CALCULATETABLE(

VALUES('_Combined'[Customer name]),

ALL('_Combined'),

YEAR('*DATE TABLE'[Date]) < YEAR(currentDate)

)

VAR newCustomers = EXCEPT(currentCustomers, pastCustomers)

RETURN

CALCULATE(

SUM('_Combined'[Amount EUR]),

FILTER(

'_Combined',

'_Combined'[Customer name] IN newCustomers

)

Re: New Customer count and sales calculation

123abc — Tue, 12 Dec 2023 14:23:13 GMT

Your measures for calculating new customers and new customer sales seem to be on the right track. However, there are a few improvements that can be made, especially in terms of performance and correctness.

New Customers Year Measure:
- The calculation of prevYear seems correct, but it's always a good practice to use a date function to get the previous year.
- You can simplify the pastCustomers calculation using the DATESBETWEEN function to filter the customers who made a purchase in the previous year.
Here's an updated version:

New Customers Year =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentDate = MAX('*DATE TABLE'[Date])
VAR prevYearStart = DATEADD(currentDate, -1, YEAR)
VAR prevYearEnd = DATEADD(currentDate, -1, YEAR) - 1
VAR pastCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
ALL('_Combined'),
DATESBETWEEN('*DATE TABLE'[Date], prevYearStart, prevYearEnd)
)
VAR newCustomers = EXCEPT(currentCustomers, pastCustomers)
RETURN
COUNTROWS(newCustomers)

New Customers Sales Measure:

The pastCustomers calculation can also be simplified using DATESBETWEEN.
You can use the SUMX function to directly calculate the sum of sales for new customers.

Here's an updated version:

New Customers Sales =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentDate = MAX('*DATE TABLE'[Date])
VAR prevYearStart = DATEADD(currentDate, -1, YEAR)
VAR prevYearEnd = DATEADD(currentDate, -1, YEAR) - 1
VAR pastCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
ALL('_Combined'),
DATESBETWEEN('*DATE TABLE'[Date], DATE(YEAR(currentDate), 1, 1), prevYearEnd)
)
RETURN
SUMX(
FILTER(
'_Combined',
'_Combined'[Customer name] IN currentCustomers && '_Combined'[Customer name] IN newCustomers
),
'_Combined'[Amount EUR]
)

These changes should enhance the performance and correctness of your measures. Always remember to test your measures with different scenarios to ensure they provide the expected results.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: New Customer count and sales calculation

mbacicci — Tue, 12 Dec 2023 15:08:09 GMT

Thank you! I will test them and let you know.
About your changes, do I see correctly that you're comparing only to previous year? The count of new customers (and their sales) should be year over year based on all previous years, not only the last one.
In short: we would consider ABC a new customer in 2022 only a customer that has never bought before, back until the database has invoicing data. Not only if ABC didn't purchase on 2021.

Re: New Customer count and sales calculation

123abc — Wed, 13 Dec 2023 06:30:55 GMT

I see, thank you for the clarification. If you want to identify customers who are new in the current year based on their entire purchase history, you should modify the measures accordingly. Here's the updated code:

New Customers Year =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentDate = MAX('*DATE TABLE'[Date])

VAR allPreviousCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
ALL('_Combined'),
'*DATE TABLE'[Date] < DATE(YEAR(currentDate), 1, 1)
)

VAR newCustomers = EXCEPT(currentCustomers, allPreviousCustomers)

RETURN
COUNTROWS(newCustomers)

New Customers Sales =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentDate = MAX('*DATE TABLE'[Date])

VAR allPreviousCustomers = CALCULATETABLE(
VALUES('_Combined'[Customer name]),
ALL('_Combined'),
'*DATE TABLE'[Date] < YEAR(currentDate)
)

RETURN
CALCULATE(
SUM('_Combined'[Amount EUR]),
'_Combined'[Customer name] IN VALUES('_Combined'[Customer name])
)

In these revised measures, I've replaced the calculation for allPreviousCustomers to include all customers who made purchases before the current year, not just the previous year. This should give you the count of new customers and their sales based on their entire purchase history up to the current date.

Please test these changes and let me know if they meet your requirements. If you have any further questions or concerns, feel free to ask!

Re: New Customer count and sales calculation

mbacicci — Wed, 13 Dec 2023 14:17:23 GMT

Clear, thanks 123abc!

What is the main difference between the measures I was using and the last ones you've sent?

The New Customers Year works well and shows a slighly lower number compared to my original one. New Customers Sales shows total invoiced amounts per year instead.

I have another table connected which is called _Products, and I would like to filter the new customers per year also based on the column Product Origin they've been invoiced for. Is there a way to include this in the both measures?

Re: New Customer count and sales calculation

123abc — Thu, 14 Dec 2023 05:33:56 GMT

I'm glad to hear that the New Customers Year measure is working well. Regarding the difference between the measures, the main change I made is in how we identify past customers. Instead of looking only at the previous year, we now consider all customers who made a purchase before the current year.

Now, for incorporating the _Products table and filtering new customers based on the Product Origin column, we can modify the measures accordingly. Here are the updated measures:

New Customers Year =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentDate = MAX('*DATE TABLE'[Date])
VAR currentYear = YEAR(currentDate)

VAR allPastCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
ALL('_Combined'),
'*DATE TABLE'[Date] < DATE(currentYear, 1, 1)
)

VAR newCustomers = EXCEPT(currentCustomers, allPastCustomers)

RETURN
COUNTROWS(
FILTER(
newCustomers,
CALCULATE(
COUNTROWS('_Products'),
ALL('_Products'),
'_Combined'[Customer name] = newCustomers[Customer name]
) > 0
)
)

New Customers Sales =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentDate = MAX('*DATE TABLE'[Date])
VAR currentYear = YEAR(currentDate)

VAR allPastCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
ALL('_Combined'),
'*DATE TABLE'[Date] < DATE(currentYear, 1, 1)
)

VAR newCustomers = EXCEPT(currentCustomers, allPastCustomers)

RETURN
CALCULATE(
SUM('_Combined'[Amount EUR]),
FILTER(
'_Combined',
'_Combined'[Customer name] IN VALUES('_Combined'[Customer name]) &&
YEAR('_Combined'[Date]) = currentYear &&
CALCULATE(
COUNTROWS('_Products'),
ALL('_Products'),
'_Combined'[Customer name] = VALUES('_Combined'[Customer name])
) > 0
)
)

In these updated measures, I added a filter based on the _Products table in both measures. It checks if the customer has been invoiced for any product with a non-zero count in the Product Origin column. Adjust the column names as needed based on your actual data model.

Please test these measures and let me know if they now work as expected.

Re: New Customer count and sales calculation

mbacicci — Thu, 14 Dec 2023 06:49:37 GMT

It makes sense, thank you again 123abc 😊

New Customers Year now gives me the error "cannot find table 'newcustomers'. I think referring to this.

'_Combined'[Customer name] = newCustomers[Customer name]

As a note, the table _Products doesn't have Customer name. It's just a list of all product information for products sold, and it has an active relationship with _Combined with a column called Item No.

New Customers Sales is still showing total invoiced amounts for those years.

Let me know if you need any more information about the

Re: New Customer count and sales calculation

mbacicci — Wed, 20 Dec 2023 07:27:18 GMT

Hi 123abc , sorry for bothering still 😅
I've tried different options but I'm still not able to build the right measure for what I mentioned here. May I ask you for your help once more?
Thank you!

Re: New Customer count and sales calculation

123abc — Wed, 20 Dec 2023 10:54:56 GMT

Let's address these issues step by step.

1. Error in New Customers Year Measure:

The error is occurring because newCustomers is not a table with a Customer name column. Instead, it's a list of distinct customer names. You cannot directly compare a table with a column in DAX without using functions like IN.

Let's modify the New Customers Year with Product Origin measure to correct this:

New Customers Year with Product Origin =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))
VAR selectedProductOrigin = "YourSelectedProductOriginValue" -- Replace with the product origin value you want to filter by

VAR newCustomers =
FILTER(
currentCustomers,
[First Purchase Year] = currentYear &&
CALCULATE(
COUNTROWS('_Combined'),
FILTER(
'_Combined',
'_Combined'[Customer name] = currentCustomers && -- Directly comparing with the list of current customers
'_Combined'[Product Origin] = selectedProductOrigin
)
) > 0 -- This ensures that the new customer has purchased the selected product origin at least once
)

RETURN
COUNTROWS(newCustomers)

New Customers Sales Issue:

For the New Customers Sales with Product Origin measure, the aim is to get the sales amount for new customers of the specified product origin in the current year.

Let's correct this by explicitly filtering the sales for the new customers of the specified product origin:

New Customers Sales with Product Origin =
VAR currentCustomers = VALUES('_Combined'[Customer name])
VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))
VAR selectedProductOrigin = "YourSelectedProductOriginValue" -- Replace with the product origin value you want to filter by

RETURN
CALCULATE(
SUM('_Combined'[Amount EUR]),
'_Combined'[Customer name] IN newCustomers
)

These adjustments should help you get the desired results. Make sure to replace "YourSelectedProductOriginValue" with the actual product origin value you wish to filter by.

Re: New Customer count and sales calculation

123abc — Wed, 20 Dec 2023 10:55:53 GMT

Of course! Let's simplify and address this step-by-step.

Your main requirement is to calculate the count of new customers for a specific year and also to calculate the sales amount for those new customers based on a selected product origin.

1. New Customers Year with Product Origin:

First, let's identify the count of new customers for the specific year and product origin.

New Customers Year with Product Origin =
VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))
VAR selectedProductOrigin = "YourSelectedProductOriginValue" -- Replace with the product origin value you want to filter by

VAR newCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
'_Combined'[Product Origin] = selectedProductOrigin,
NOT EXISTS(
CALCULATETABLE(
'_Combined',
'_Combined'[Product Origin] = selectedProductOrigin,
YEAR('_DATE TABLE'[Date]) < currentYear
),
'_Combined'[Customer name]
)
)

RETURN
COUNTROWS(newCustomers)

2. New Customers Sales with Product Origin:

Next, let's calculate the sales amount for those new customers in the specified year and product origin.

New Customers Sales with Product Origin =
VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))
VAR selectedProductOrigin = "YourSelectedProductOriginValue" -- Replace with the product origin value you want to filter by

RETURN
CALCULATE(
SUM('_Combined'[Amount EUR]),
'_Combined'[Customer name] IN newCustomers
)

In both measures, I'm using the NOT EXISTS pattern to identify customers who have not made a purchase of the selected product origin before the current year. This should provide you with the count of new customers and their sales based on the selected product origin for the specified year.

Please replace "YourSelectedProductOriginValue" with the actual product origin value you want to filter by when using these measures.

Re: New Customer count and sales calculation

mbacicci — Wed, 20 Dec 2023 13:39:41 GMT

I get an error to EXISTS saying it's not a function, so the overall measure has the banner "This is not a valid DAX expression".

How could we change it?
Thank you 123abc

Re: New Customer count and sales calculation

123abc — Thu, 21 Dec 2023 05:21:24 GMT

Instead of using the EXISTS function, we can use the INTERSECT function to determine the intersection of two tables. This will allow us to identify new customers more efficiently.

Let's rewrite the measures using INTERSECT:

1. Identify New Customers for Each Year:

New Customers Year =
VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))

VAR allPastCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
FILTER(
ALL('_Combined'),
YEAR('_Combined'[Date]) < currentYear
)
)

VAR newCustomers =
EXCEPT(
VALUES('_Combined'[Customer name]),
allPastCustomers
)

RETURN
COUNTROWS(newCustomers)

2. Adjusting for Product Origin Filter:

New Customers Year with Product Filter =
VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))

VAR allPastCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
FILTER(
ALL('_Combined'),
YEAR('_Combined'[Date]) < currentYear
)
)

VAR potentialNewCustomers =
EXCEPT(
VALUES('_Combined'[Customer name]),
allPastCustomers
)

VAR newCustomers =
CALCULATETABLE(
potentialNewCustomers,
FILTER(
potentialNewCustomers,
'_Combined'[Item No] IN RELATEDTABLE('_Products')[Item No] &&
'_Combined'[Product Origin] IN VALUES('_Products'[Product Origin])
)
)

RETURN
COUNTROWS(newCustomers)

3. Sales from New Customers:

New Customers Sales with Product Filter =
VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))

VAR allPastCustomers =
CALCULATETABLE(
VALUES('_Combined'[Customer name]),
FILTER(
ALL('_Combined'),
YEAR('_Combined'[Date]) < currentYear
)
)

VAR potentialNewCustomers =
EXCEPT(
VALUES('_Combined'[Customer name]),
allPastCustomers
)

RETURN
CALCULATE(
SUM('_Combined'[Amount EUR]),
FILTER(
'_Combined',
'_Combined'[Customer name] IN newCustomers
)
)

In these revised measures, I replaced the use of INTERSECT with EXCEPT to identify potential new customers and then further filtered them based on the Product Origin criteria from the _Products table.

Please try implementing these adjusted measures and let me know if they resolve the issue!

Re: New Customer count and sales calculation

mbacicci — Thu, 21 Dec 2023 07:08:12 GMT

2. Adjusting for Product Origin Filter: I'm getting this error now for this.

When I try and find Item No and Product Origin, the columns are not available while only measures in that table are. How can this be re-adapted?

The syntax for '[Item No]' is incorrect. (DAX(VAR currentYear = YEAR(MAX('*DATE TABLE'[Date]))VAR allPastCustomers =CALCULATETABLE(VALUES('_Combined'[Customer name]),FILTER(ALL('_Combined'),YEAR('_Combined'[Invoice date]) < currentYear))VAR potentialNewCustomers =EXCEPT(VALUES('_Combined'[Customer name]),allPastCustomers)VAR newCustomers =CALCULATETABLE(potentialNewCustomers,FILTER(potentialNewCustomers,'_Combined'[Item No] IN RELATEDTABLE('_Products')[Item No] &&'_Combined'[Product Origin] IN VALUES('_Products'[Product Origin])))RETURNCOUNTROWS(newCustomers))).