topic Finding Monthly Unique New Customers in DAX Commands and Tips

Finding Monthly Unique New Customers

craigology — Thu, 30 Dec 2021 22:50:44 GMT

Scenario: EOM reporting for December and working from one master spreadsheet where new customers are inputted with new orders. The same customer might have ordered something in June and November so they wouldn't be a new customer but I'm trying to get to a number of how many net-new customers joined in December.

Here's the steps I use to find unique customer additions each month in excel:

1. Isolate all December (12/1/2021 - 12/31/2021) customers that were entered into the spreadsheet and dedupe that list to get unique customer ids (example yeild of 100 unique customers)

2. Isolate all customers that were entered into the spreadsheet earlier than December (1/1/2021 - 11/30/2021) and dedupe that list to get unique customer ids (example yeild of 3000 unique customers)

3. Vlookup step 1 against step 2

4. If vlookup doesn't produce any matches then = New Customer

5. If vlookup produces matches then = Existing Customer (doesn't need to be counted)

I'd like all existing date filter drop-downs to be able to still change these numbers and graphs as they do now with other visuals in the PBI report. So, if I were to filter for February or August it would give me those month's unique customers.

Has anyone already put together anything like this before. I've had some success with doing easy DAX measures in PBI, but this one is a mystery to me. Any help would be appreciated.

Re: Finding Monthly Unique New Customers

OwenAuger — Fri, 31 Dec 2021 02:03:54 GMT

Hi craigology

I recommend you check out the New Customers pattern on DAX Patterns:

https://www.daxpatterns.com/new-and-returning-customers/

When writing your measures, you need to choose whether the calculation should be relative/absolute - described on the above page.

Post back if you need help adapting to your specific data model 🙂

Regards,

Owen

Re: Finding Monthly Unique New Customers

Hanson97 — Fri, 31 Dec 2021 07:03:48 GMT

Hi craigology

This is quite simple in Power BI

1. Created a table called "Sample" to replicate your dataset. You can replace the col/table names with your dataset.

2. Create a calculated column "Month Index" to keep track of month passed from a reference date (jan 2021 in my case).

Month Index = DATEDIFF(DATE(2020,1,1), 'Sample'[Input Date ],MONTH)

3. Create another calculated colmumn to check if users are Old/new

Customer Profile = VAR customerName = 'Sample'[Customer Name] VAR monthIndex = 'Sample'[Month Index] VAR previousCount = CALCULATE(countrows('Sample'), filter(all('Sample'), 'Sample'[Customer Name] = customerName && 'Sample'[Month Index] < monthIndex)) RETURN IF( previousCount > 0, "Old Customer", "New Customer")

4. Create a label visual and add the "Distinct Count" of Customer Name column. Also add an additional filter on the visual to only count distinct count for "Customer Profile" to be "New Customer".

Thats It! You can filter the table now using month filter (or any other filter) to see the uniqe count between the specified time span.

If this helped you, mark this as the solution to help others refer.

Thanks,
Hanson

Re: Finding Monthly Unique New Customers

craigology — Tue, 11 Jan 2022 03:03:51 GMT

Thanks so much for this Hanson97. You saved me! 🙂

Re: Finding Monthly Unique New Customers

craigology — Tue, 11 Jan 2022 03:04:16 GMT

Thanks OwenAuger! 🙂