Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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)”)
Hi @Anonymous ,
From your sample data and description I assume the following constraints:
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
9 |
User | Count |
---|---|
18 | |
14 | |
14 | |
13 | |
12 |