cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianmelb99 Frequent Visitor
Frequent Visitor

Historic open claims

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ianmelb99 Frequent Visitor
Frequent Visitor

Re: Historic open claims

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..

3 REPLIES 3
Highlighted
adamflath Member
Member

Re: Historic open claims

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 IDOpen DateClosed DatePoint Time Date (Report Date)
123xyz1/1/2016 3/31/2016
123xyz1/1/2016 6/30/2016
123xyz1/1/20169/15/20169/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 Frequent Visitor
Frequent Visitor

Re: Historic open claims

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..

adamflath Member
Member

Re: Historic open claims

Ah, nice work around!