Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

Need Help

2 Replies

  • Martin_D's avatar
    Martin_D
    Solution Sage
    3 years ago

    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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This works great. Thank you Martin_D