Check if customer id exist in next month

[Anonymous]

Hi Everyone,

Im pretty new in powerbi and been struugling with an issue i have.

I have a table with customer_id,transaction_date and debt_indicator. You will have a 1 if in debt and 0 if out of debt. Please help me with the DAX to check if the customers only in the selected month are still in debt the next month or the next 3 months. So if 5 customers were in debt in december, are the same 5 customers still in debt in January.

[parry2k · Accepted solution]

@Anonymous solution attached, the core of the dax is here:

Count = 
VAR __selectedMonthCustomerWithDebt = 
CALCULATETABLE ( 
    VALUES ('Table'[customer_id] ), 
    TREATAS ( VALUES ( 'Calendar'[Date] ), 'Table'[transaction_date] ),
    'Table'[debt_ind] = 1
)
VAR __nextMonthCustomerWithDebt = 
CALCULATETABLE ( 
    VALUES ( 'Table'[customer_id] ), 
    TREATAS ( NEXTMONTH ( 'Calendar'[Date] ), 'Table'[transaction_date] ),
    'Table'[debt_ind] = 1
)
RETURN
COUNTROWS ( 
    INTERSECT ( __nextMonthCustomerWithDebt, __selectedMonthCustomerWithDebt )
)

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

THANK YOU!!

[parry2k]

@Anonymous what made you compare Dec  with Jan, what is the logic? Would you select a month that you want to compare or how it will work?

[Anonymous]

You will select a month and compare it to the next month

[Greg_Deckler]

@Anonymous Would need sample data to be more specific but it sounds like you need to use ALL or ALLEXCEPT in order to break out of the current filter context and "look ahead"

[Anonymous]

@Greg_Deckler @parry2k please see sample data below

customer_id	transaction_date	debt_ind
57162223	06/12/2021	1
29393466	08/12/2021	0
86555689	08/12/2021	0
57162223	09/12/2021	1
57162223	10/12/2021	1
1086688	10/12/2021	1
57162223	10/12/2021	1
930306	23/12/2021	0
59457085	27/12/2021	0
57562487	31/12/2021	1
60084507	31/12/2021	1
58159987	02/01/2022	0
57912125	03/01/2022	0
58705069	04/01/2022	1
60084507	05/01/2022	0
29513792	06/01/2022	0
57768319	07/01/2022	0
2462932	08/01/2022	1
60084507	09/01/2022	0
60084507	10/01/2022	0
1086688	11/01/2022	0
57562487	12/01/2022	0
1086688	13/01/2022	0
57162223	14/01/2022	1

from this data only 3 distinct account_ids had a debt status of 1 in december and i want to calculate how many of those 3 account_ids still have a status of 1 in the the following month

[parry2k]

@Anonymous I would recommend posting some sample data with the expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490