Forum Discussion
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..
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?
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..
Ah, nice work around!