Historic open claims

[ianmelb99]

Hi,

I am new to Dax and having a problem calculating historic open claims report.

I have  a fact table of insurance claims. Each row is a unique claim record number and includes a date opened and date closed field. Claims that are not yet closed have a blank in the closed column.

I want my report to include how many open claims existed over past weeks in the form of a bar chart. So for a given day/period in the past how many claims were open but not closed.

I have a dimension date table linked to my open claim date field.

suggestions on how to solve this would be appreciated. Thanks.

[Anonymous]

You can do that.... but you have to realize that it is much more complicated than you might think.  Remember, a claims status can change every day, so you would technically need a new and unique "Loss Run" report for each period you want on your bar chart. (this is called the Point in Time of your data) So before you get the dax equations, you need to make sure you data has the correct information.  

1. How many Point in Time periods are you looking at?

2. Are all of the Point in Time periods loaded into a single sheet?

Here is an example of ONE CLAIM over 3 periods of analysis:

Claim ID	Open Date	Closed Date	Point Time Date (Report Date)
123xyz	1/1/2016		3/31/2016
123xyz	1/1/2016		6/30/2016
123xyz	1/1/2016	9/15/2016	9/30/2016

So you can see if your data looked like this, you would have 3 points on the X axis to represent the dates of if the claim was Opened or closed (3/31 6/30 and 9/30).

Your bar chart for open claims would have a bar with a value of "1" for the dates 3/31 and 6/30 and then no bar for 9/30 since the claim is now closed.

The DAX to arrive at that is very easy, the real question is how is your data organized with the Point in Time? 

[ianmelb99 · Accepted solution]

Adam,

Thanks for your comments. Makes more sense now.

I have soved it by

- creating a fake closedate column in my claims table which is the actual close date for closed claims and today for open claims.

- creating a date table called OpenClaims and linking it to the open date in the claims claims table.

- in my new date table I created a calculated column using

OpenClaimsCount = COUNTROWS(FILTER('ClaimsTable','ClaimsTable'[Date Claim Entered]<='OpenClaims'[Date]&&'ClaimsTable'[FakeCloseDate]>='OpenClaims'[Date]))

My OpenClaims table now has a number of open claims for every date

I can now report at a day granular level or average for week etc.

Slowly getting my head around DAX - Phew..

[Anonymous]

Ah, nice work around!