cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Need help with creating a measure to calculate roll rate % of delinquent accounts

Hi folks,

I am currently stuck in a situation where I am trying to calculate the roll rate % of delinquent accounts in Power BI. I have delinquency listed out in catergories: 1-30, 31-60, 61-90, 90+ and so on along with dates. I want to create a measure which would return the Roll Rate % value for each category (1-30, 31-60, 61-90, 90+ and so on) based on the amounts. I was able to calculate them in Excel and have tried several measures in Power BI but no luck.

For instance, to calculate 1-30 roll rate%: I divided 1-30 amount of current month by Current amount of previous month. Please refer to the screenshots to see the sample calculation.

Also, I am attaching a table with sample dataset for reference. I would highly appreciate any kind of assistance with this.

 Date Current 1-30 1-30 Roll rate % 31-60 31-60 Roll rate % 61-90 61-90 Roll rate % 91+ 91+ roll rate % 2019-01-31 57000 69000 124374 38732 8686 2019-02-28 100000 45000 79% 3984835 5775% 23546 19% 54366 140% 2019-03-31 157000 800000 800% 48757 108% 55765 1% 67676 287% 2019-04-30 400700 700000 446% 564535 71% 323423 663% 77667 139% 2019-05-31 500900 478096 119% 42454 6% 5765756 1021% 6567 2% 2019-06-30 800700 345453 69% 464567 97% 45344 107% 7455 0% 2019-07-31 900100 2335346 292% 7876876 2280% 7687 2% 67765 149% 2019-08-31 1001400 253564 28% 555466 24% 665677 8% 56363 733% 2019-09-30 1120000 233553 23% 6767687 2669% 42546 8% 76567 12%

2 ACCEPTED SOLUTIONS
Super User
``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFZjoNADETvwneQ3C5vfZYo97/GeIEJEUhuNX6uKvN+H0xrn7ROrON1qBNRVttTFwtc8oBwcNawsOPzujE+OaqNaPpFp2KHBDRPDBWryQJ7khjBdSvG/4hw9QLUrQaY5/MA5US3EXn3+w2qibYkGMKoT27mD1SvkER7pDxolzlhUen4pq51Y/pD2ojGLYrs15KQFDXv5JAa4aL6AH0kU281yEA2loKHW751ytpBK/AXjWtDicqwGc/apuZO22XZ7GUZDA92j9+1+FpO6bbhXmfrCc+f8cn6+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Current = _t, #"1-30" = _t, #"31-60" = _t, #"61-90" = _t, #"91+ " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Current", type number}, {"1-30", type number}, {"31-60", type number}, {"61-90", type number}, {"91+ ", type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"1-30 %", Percentage.Type}, {"31-60 %", Percentage.Type}, {"61-90 %", Percentage.Type}, {"91+ %", Percentage.Type}})
in
#"Changed Type1"``````

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi Ashish,

Thank you so much for providing guidance. I tried this and it worked just fine :). However, I want the values to change accordingly to the filters ( such as Lending partners, Provinces, etc.) I choose, is there a solution to make it dynamic according to the chosen filters? For instance, in excel I had applied filters in the pivot table and when I selected different dropdown options through filters, the output value changed accordingly.

Thank you!

Super User

"However, I want the values to change accordingly to the filters ( such as Lending partners, Provinces, etc.) "

Frequent Visitor

Actually, never mind I finally figured it out. Utilized filter function to filter out each days_delq and calculate from there on.

I am so thankful to both of you for providing guidance with my situation. Kudos! :))

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User
``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFZjoNADETvwneQ3C5vfZYo97/GeIEJEUhuNX6uKvN+H0xrn7ROrON1qBNRVttTFwtc8oBwcNawsOPzujE+OaqNaPpFp2KHBDRPDBWryQJ7khjBdSvG/4hw9QLUrQaY5/MA5US3EXn3+w2qibYkGMKoT27mD1SvkER7pDxolzlhUen4pq51Y/pD2ojGLYrs15KQFDXv5JAa4aL6AH0kU281yEA2loKHW751ytpBK/AXjWtDicqwGc/apuZO22XZ7GUZDA92j9+1+FpO6bbhXmfrCc+f8cn6+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Current = _t, #"1-30" = _t, #"31-60" = _t, #"61-90" = _t, #"91+ " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Current", type number}, {"1-30", type number}, {"31-60", type number}, {"61-90", type number}, {"91+ ", type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"1-30 %", Percentage.Type}, {"31-60 %", Percentage.Type}, {"61-90 %", Percentage.Type}, {"91+ %", Percentage.Type}})
in
#"Changed Type1"``````

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Frequent Visitor

Hi,

First I would like to thank you for providing assitance in my case.
When I tried to paste the above query, I was encounted with an error message. I am attaching a screenshot of the message. Also, my reference table is: 'Data' (seen in the screenshot), so I copied the source query from there and pasted it instead of "

``Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZFZjoNADETvwneQ3C5vfZYo97/GeIEJEUhuNX6uKvN+H0xrn7ROrON1qBNRVttTFwtc8oBwcNawsOPzujE+OaqNaPpFp2KHBDRPDBWryQJ7khjBdSvG/4hw9QLUrQaY5/MA5US3EXn3+w2qibYkGMKoT27mD1SvkER7pDxolzlhUen4pq51Y/pD2ojGLYrs15KQFDXv5JAa4aL6AH0kU281yEA2loKHW751ytpBK/AXjWtDicqwGc/apuZO22XZ7GUZDA92j9+1+FpO6bbhXmfrCc+f8cn6+QM=", BinaryEncoding.Base64), Compression.Deflate))``

, do you think the source I put in is accurate? I would be very grateful to you if you could help me figure this out as I have been scratching my head with for days now.
Thank you!

Super User

In line 4 replace "Source" with "Data_Sheet"

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors