Data preparation, user input and global filters on dashboard

[minhhieule89]

Hi all, I have some questions about Power BI, I hope you can help me with these

I'm an financial analyst and I'm trying to re-create the vintage loss curve (accumulated loss curve) in Power BI that I currently have in Excel. for those who are not familiar with the concept, I have a loan table with contract ID, contract date and loan amount, a 2nd table called loss with the contract ID, loss date and loss amount, the goal is to create a loss curve which is the accumulated loss over time of certain pool devided into the sum of loan amount of the same pool

I was able to do the calculation but certain thing stopped me from going beyond that

1. I want the user to have the flexibility to configure the pool of loan to draw the chart e.g. user A pick a date range and certain criteria then the formula just update accordingly

2. I created the dashboard with a numbers of reports but when I tried to apply a filter to the dashboard, certain report (the one above specifically) does not change, I cannot figure out why, are those filters only work on non-formula based data?

3. Adding additional column (raw data column) to an existing table is not possible, I tried everything from refresh, power querry, etc. none of them does what I want

any help would be greatly appreciated

Mike

[v-joesh-msft]

Hi @minhhieule89 ,

The link is invalid, could you share the expected result again? You need to have cloud storage such as Onedrive or Google Drive where you can put your file. After that, you create a shareable link that you can insert in your post enabling people to download.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-joesh-msft]

Hi @minhhieule89 ,

I'm a little confused by your description. Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[minhhieule89]

thanks for replying, here is the result chart in Excel, I can also include the excel file but I don't see where to attach file. below is the tables

https://ibb.co/kH1DjD6 

here are the three tables, Loan, Loss and Transactions

Loan

ContractID	OriginationDate	OriginationAmount	Tier
1	1-1-2018	10,000	1
2	2-1-2018	10,000	2
3	3-1-2018	10,000	3
4	4-1-2018	10,000	1
5	5-1-2018	10,000	2
6	6-1-2018	10,000	3
7	7-1-2018	10,000	1
8	8-1-2018	10,000	2
9	9-1-2018	10,000	3
10	10-1-2018	10,000	1
11	11-1-2018	10,000	2
12	12-1-2018	10,000	3
13	1-1-2019	10,000	1
14	2-1-2019	10,000	2
15	3-1-2019	10,000	3
16	4-1-2019	10,000	1
17	5-1-2019	10,000	2
18	6-1-2019	10,000	3
19	7-1-2019	10,000	1
20	8-1-2019	10,000	2
21	9-1-2019	10,000	3
22	10-1-2019	10,000	1
23	11-1-2019	10,000	2
24	12-1-2019	10,000	3

Loss

ContractID	LossDate	LossAmount	Origination Date
1	3-3-2018	5,000	1-1-2018
4	6-7-2018	4,500	4-1-2018
6	8-4-2018	6,000	6-1-2018
7	10-2-2018	9,000	7-1-2018
8	12-12-2018	3,000	8-1-2018
3	2-2-2019	6,500	3-1-2018
5	4-6-2019	7,000	5-1-2018
14	6-1-2019	7,000	2-1-2019
18	7-7-2019	4,500	6-1-2019
21	8-8-2019	5,000	9-1-2019
24	9-5-2019	4,000	12-1-2019

Transactions

ContractID	OriginationDate	TransactionType	TransactionDate	TransactionAmount
3	3-1-2018	Fee	5-7-2018	200
3	3-1-2018	Recovery	6-7-2018	400
5	5-1-2018	Fee	6-5-2018	200
5	5-1-2018	Fee	6-29-2018	200
5	5-1-2018	Recovery	8-9-2018	500
6	6-1-2018	Fee	5-29-2018	200
6	6-1-2018	Recovery	6-7-2018	350
6	6-1-2018	Recovery	7-4-2018	150
7	7-1-2018	Fee	7-2-2018	200
8	8-1-2018	Fee	10-10-2018	300
12	12-1-2018	Fee	6-1-2019	200
12	12-1-2018	Fee	7-7-2019	200
12	12-1-2018	Recovery	9-7-2019	150
13	1-1-2019	Fee	8-7-2019	200
14	2-1-2019	Fee	9-6-2019	200
14	2-1-2019	Fee	10-1-2019	200
14	2-1-2019	Recovery	7-5-2019	300
15	3-1-2019	Fee	10-7-2019	120
15	3-1-2019	Recovery	11-17-2019	120
16	4-1-2019	Fee	11-5-2019	100
17	5-1-2019	Fee	11-10-2019	200