Forum Discussion
Rating migration Power BI view
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.
Hi,
PBI file attached.
Hope this helps.
11 Replies
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.
Input data and desired solution added to the original post
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.
Input data and desired solution added to the original post
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 selectedHi,
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,
Gaurav
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.
