cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Identify new customers using date differences

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)”)

2 REPLIES 2
Super User

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
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``````

Anonymous
Not applicable

This works great. Thank you @Martin_D

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.