Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Everyone,
I'm dealing with a monthly delinquency tracking situation that I need help with.
The concept is to create a matrix contaning groups, based on the number of Days Past Due, for both the Previous months ending balance, and the most recent month completed.
DPD (DaysPastDue) groups: A0, B1-9, C10-29, D30-59, E60-89, F90+
What i need is to begin with the ending balance from the prior month (Starting bucket), and track the performance of those balacnces over the next 30 days:
***Note: The Starting Bucket & Total (principal) column is from August 2020 [in Direction going Down], and the A0 to F90+ [in direction going Right] is reflecting September 2020. Example: In Aug 2020, we ended the month with $111.5Mil in current status (A0 Bucket). In Sept 2020, of the $115.5Mil, we now have $15Mil that went entered the 1 to 9 days past due (B1-9 bucket).
The above was generated via python script that broke, due to some changes to our loan book, and its very difficult to unravel someone else's work. Therefore, i need to reprodue it using our base tables via Power BI.
What I have:
I've created a group for the DPD (Days Past Due) already.
The amount is based on the Pricipal_Balance of all loans combined
The date is based on loan Aging_date (monthly totals).
What I need:
Dax to calculate the PRIOR end of month principal total, within each DPD Bucket as it ending the prior month.
Dax for calculating and populating each cell of the Matrix, based on how the principal balance has progressed forward into the next month.
Assistance in figuring this out would be appreciated.
@qbarnes What does the raw data look like? Are you able to paste sample table here or link to sample file?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |