Forum Discussion
Rating migration Power BI view
Hi,
PBI file attached.
Hope this helps.
SAMPLE INPUT DATA
-----------------
One row per entity per date. Sample below shows two dates:
| Entity_ID | Rating | Date | Book Value |
|-----------|--------|-------------|------------|
| E001 | AAA | 30-Sep-2025 | 1,000,000 |
| E002 | BBB | 30-Sep-2025 | 500,000 |
| E003 | AA | 30-Sep-2025 | 750,000 |
| E004 | A | 30-Sep-2025 | 300,000 |
| E005 | BB | 30-Sep-2025 | 200,000 |
| E006 | BBB | 30-Sep-2025 | 450,000 |
| E001 | AA | 31-Dec-2025 | 1,100,000 |
| E002 | BB | 31-Dec-2025 | 520,000 |
| E003 | AA | 31-Dec-2025 | 780,000 |
| E004 | BBB | 31-Dec-2025 | 310,000 |
| E005 | B | 31-Dec-2025 | 210,000 |
| E007 | A | 31-Dec-2025 | 400,000 |
DESIRED OUTPUT — Count of Obligors
------------------------------------
Current Date : 31-Dec-2025
Previous Date: 30-Sep-2025
| Current \ Previous | AAA | AA | A | BBB | BB | NA |
|--------------------|-----|-----|-----|-----|-----|-----|
| AAA | 0 | 0 | 0 | 0 | 0 | 0 |
| AA | 1 | 1 | 0 | 0 | 0 | 0 |
| A | 0 | 0 | 0 | 0 | 0 | 1 |
| BBB | 0 | 0 | 1 | 0 | 0 | 0 |
| BB | 0 | 0 | 0 | 1 | 0 | 0 |
| B | 0 | 0 | 0 | 0 | 1 | 0 |
| NA | 0 | 0 | 0 | 1 | 0 | 0 |
DESIRED OUTPUT — Book Value
-----------------------------
Current Date : 31-Dec-2025
Previous Date: 30-Sep-2025
| Current \ Previous | AAA | AA | A | BBB | BB | NA |
|--------------------|-----------|-----------|-----------|-----------|-----------|-----------|
| AAA | 0 | 0 | 0 | 0 | 0 | 0 |
| AA | 1,100,000 | 780,000 | 0 | 0 | 0 | 0 |
| A | 0 | 0 | 0 | 0 | 0 | 400,000 |
| BBB | 0 | 0 | 310,000 | 0 | 0 | 0 |
| BB | 0 | 0 | 0 | 520,000 | 0 | 0 |
| B | 0 | 0 | 0 | 0 | 210,000 | 0 |
| NA | 0 | 0 | 0 | 450,000 | 0 | 0 |
KEY CONSTRAINTS
---------------
- Cannot duplicate the source table to achieve the result
- Need two separate date slicers — one for current date and one for previous date
- Values should switch between Count of Obligors and Book Value
using a field parameter or slicer
- Solution should work dynamically when different dates are selected
Hi,
PBI file attached.
Hope this helps.
Hi Ashish,
Thanks a lot for creating this pbix for me.
The version that you have sent works.However, when I try to replicate this logic by using the same table and column names in my Power BI file. I get blanks in the matrix view.
Could it be because when I open your pbix file, I get a prompt saying "The queries were authored with a newer version of Power BI desktop and might not work with your version. Please install the latest version to avoid errors when refreshing."
I am using the below version of Power BI:
Version: 2.148.1226.0 64-bit (October 2025)The only difference in my implementation is that I created calculated table for Rating and Rating_dup, whereas you created them in Power Query view. Should that make a difference?
Regards,
GauravYou are welcome. FIrst drag to rows, then to values and then to columns.
Hi Ashish,
I was able to figure this out. I wasn't drilling down the matrix view from the options provided in the top using arrows.
Thanks for the help!
Will this method be able to help me:
1) Identifying new and dropped entity_id
2) drill down data to see entity_ids moved from AA to A for example
3) Number of upgrades and downgrades
Regards,
Gaurav