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

[Darwinm]

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. 

Thank you in advance! 

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%

[lbendlin · Accepted solution]

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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "1-30 %", each try [#"1-30"]/#"Changed Type"[Current]{[Index]-1} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "31-60 %", each try [#"31-60"]/#"Changed Type"[#"1-30"]{[Index]-1} otherwise null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "61-90 %", each try [#"61-90"]/#"Changed Type"[#"31-60"]{[Index]-1} otherwise null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "91+ %", each try [#"91+ "]/#"Changed Type"[#"61-90"]{[Index]-1} otherwise null),
    #"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".

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Rajini_9]

Hi Ashish,

Am not able to download the link to view the calculation of roll rates%.

would you be able to copy and paste here so i can replicate in Power Bi to understand the logic.thank you

[Ashish_Mathur]

Hi,

I do not have that file.  Share some data, explain the question and show the expected result.

[Rajini_9]

thanks Ashish for quick reply.

Basically i am trying to replicate excel reports to Power BI.

Need help in the logic to create Flow rates on 12 months data.

Below is the sample snap shot for a month..

Bucket	31/05/2024	30/06/2024
0	$           272,636,283	$   405,451,761
1	$             45,069,853	$      94,650,236
2	$               2,194,434	$        4,113,874
3	$               3,182,276	$            804,972
4	$               4,562,202	$            329,291
5	$                   772,963	$            752,025
6	$                   484,132	$            231,041
	Flow rates	30/06/2024	Formuale
	M1:  1 - 29	35%	junbucket1/maybucket0
	M2: 30 - 59	9%	junbucket2/maybucket1
	M3: 60 - 89	37%	junbucket3/maybucket2
	M4: 90 - 119	10%	junbucket4/maybucket3
	M5:120 - 149	16%	junbucket5/maybucket4
	M6:150 - 179	30%	junbucket6/maybucket5

From your screen shot..i need the formuale that used to calculate 30+Roll rate,60+Roll rate., etc.,

Hope its clear.thank you

[Darwinm]

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. 

Highly appreciate your assistance! 
Thank you! 

[lbendlin]

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

Please provide sanitized sample data that fully covers your issue.

[Darwinm]

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! :))

[Ashish_Mathur]

You are welcome.

[lbendlin · Accepted solution]

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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "1-30 %", each try [#"1-30"]/#"Changed Type"[Current]{[Index]-1} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "31-60 %", each try [#"31-60"]/#"Changed Type"[#"1-30"]{[Index]-1} otherwise null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "61-90 %", each try [#"61-90"]/#"Changed Type"[#"31-60"]{[Index]-1} otherwise null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "91+ %", each try [#"91+ "]/#"Changed Type"[#"61-90"]{[Index]-1} otherwise null),
    #"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".

[Darwinm]

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! 

[lbendlin]

In line 4 replace "Source" with "Data_Sheet"