Open Claims vs Closed Claims Ratio

[Anonymous]

Hi All,

I have a requirement where we need to find claims that were closed in a year.  However, the claim could have been opened last year, or the previous years, but have not closed yet.

example: i have a claim that opened in 2016, but finally closed in 2019.  i want to add that claim as a closed claim counted in 2019.

I have a visual using opened year as a column, this works well for all open claims within that calendar year.  

Now i want to add the closed claims for that year, but the data is wrong because it is taking the claims that were opened during the same year and closed during the same year.

These are the total closed claims within a calendar year.

I need to be able to display all opened claims from previous years, that were closed in, for example, 2019, and then calculate a ratio of close claims/open claims.

Screenshot of my dataset:

How would I do this?

[smarthp29]

Open Claims = Countdistinctnoblank[claim_No]

Closed Claims = Calculate(Countdistinctnoblank(claim_No),status = "Closed")

Now your Table will be Setup_Dt, Open Claims, Closed Claims.

I am unsure if this is what you are looking for in terms of numbers. Based on the above calculation, you basically do not care when the claim was closed and all your tagging should be based on the Setup_Dt. 

[Anonymous]

@smarthp29 ,

I need to be able to display the year the claim was closed.

[smarthp29]

Create a dim_date table which has all the dates. 

Import your fact table twice and connect it with the dim_date table i.) Setup_Dt ii.) Closed_dt

Once you do that, Do the same formulas and get Open count from the setup table and closed count from the closed table.

For both the formulas, make dim_date as your home table. 

You can drag and drop the Date from dim_date and your calculated fields.

You second table could also be filtered by closed for faster processing and you can directly used Closed count from it instead of using Calculate.

Hope this one works.

[Anonymous]

thank you for that.  i have created a sample data set that might help.  i will try and test your solution.

https://www.dropbox.com/s/ihvr3zspjboly5q/openclosedclaims.pbix?dl=0

[edhans]

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum