Rating migration Power BI view

[Gaurrav9]

Hi All,

I need to create a rating migration view that displays the change in each individual rating for 2 user selected dates. 

Matrix view

Row: Current Rating

Column: Previous Rating

Values: Count or values (using slicer or field parameters)

Date slicer for current date

Date slicer for previous date

My table has entities for different quarters, one row per entity for one date. Each entity has a rating. Entities can drop or get added. 
All this information is in one table. Important fields to note:

- Entity_ID

- Rating

- Date

- Book value

 Can someone please help me create this view? If required, please define measures and/or calculated fields to achieve this. 

[Gaurrav9]

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

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Gaurrav9]

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,
Gaurav

[Ashish_Mathur]

You are welcome.  FIrst drag to rows, then to values and then to columns.

[Gaurrav9]

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

[Ashish_Mathur]

You are welcome.

[v-hashadapu]

Hi @Gaurrav9 , Thank you for reaching out to the Microsoft Community Forum.

Could you please confirm if both date slicers will always be single select? Also, do you have a fixed list of ratings with a defined sort order and should NA be explicitly included as a category in the matrix? Also, let us know if there are any existing relationships, filters or measures in your model that might affect filter context and any other relevant details.

[AnkitaaMishra]

Hi @Gaurrav9 , Could you please share a small sample of your data along with the expected output? That will help us better understand your requirement and suggest a more accurate solution.

[Gaurrav9]

Input data and desired solution added to the original post

[Ashish_Mathur]

Hi,

Please share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Gaurrav9]

Input data and desired solution added to the original post