Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@siphesihle 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 )
)
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@siphesihle 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 )
)
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
THANK YOU!!
@siphesihle 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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
You will select a month and compare it to the next month
@siphesihle 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"
@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
@siphesihle 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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |