topic Re: Identify new customers using date differences in DAX Commands and Tips

Identify new customers using date differences

Anonymous — Fri, 30 Jun 2023 15:19:26 GMT

I’m currently developing an arrears report based on the following table. I want to look at the new customers that went into arrears this week. So, I only want to include customer C this week (26/06/2023) because A and B was in arrears last week. How can I do this in a measure?

Customer	Date
A	26/06/2023
B	26/06/2023
C	26/06/2023
A	19/06/2023
B	19/06/2023

I’m thinking of a measure using Dateadd.

New customers in arrears=

Var CurrentDate = selectedvalue (‘Table’[Date])

Return

Calculate(count(‘table’[Customer]), “check if each company has date = dateadd(CurrentDate, -7, Days)”)

Re: Identify new customers using date differences

Martin_D — Fri, 30 Jun 2023 16:11:31 GMT

Hi Anonymous ,

From your sample data and description I assume the following constraints:

You only want to compare with the directly previous week, not with all previous weeks
You have weekly data, every 7 days, so you only need to compare with exactly 7 days earlier, not with all dates in the previous week

Then try the following:

Count of new customers = VAR _selectedWeek = MAX ( 'Table'[Date] ) VAR _previousWeek = _selectedWeek - 7 VAR _customersSelectedWeek = CALCULATETABLE ( VALUES ( 'Table'[Customer] ), 'Table'[Date] = _selectedWeek ) VAR _customersPreviousWeek = CALCULATETABLE ( VALUES ( 'Table'[Customer] ), 'Table'[Date] =_previousWeek ) VAR _newCustomers = EXCEPT ( _customersSelectedWeek, _customersPreviousWeek ) VAR _countOfNewCustomers = COUNTAX ( _newCustomers, [Customer] ) RETURN _countOfNewCustomers

Re: Identify new customers using date differences

Anonymous — Mon, 03 Jul 2023 09:20:33 GMT

This works great. Thank you Martin_D