How to count the number of claims a month that closed during the month.

[BuffaloAnalyst]

I'm starting to peak with my PowerBI knowledge and I'm stuck with a DAX formula to calculate the number of insurance claims closed during a month. The part where I'm stuck is that a claim can close and then reopen a few days later (Perhaps to make another payment) and then close again. If a claim closes, reopens and closes again during a month I only want to count it once. 

I have attached an example data set with some fake data along with a table called "expected results" with how the results should look based on some Excel work. The RN is a rownumber over the CLAIM as there can be more than one transaction a day so using the MAX(RN) would be the prefered method.

From the claims closed in a month I would then want to be able to drill through to a new page and see mre details about the claims which is why there is a DimClaim table with mroe information.

FactClaimStatus
CLAIM	DATE OPENED	DATE CLOSED	DATE REOPENED	TRANS DATE	SUFFIX STATUS	NEW COUNTER	CLOSED COUNTER	PENDING COUNTER	RN
A	1/12/2024	NULL	NULL	1/12/2024	OPEN	1	0	1	1
A	NULL	1/15/2024	NULL	1/15/2024	CLOSED	0	1	0	2
A	NULL	NULL	1/23/2024	1/23/2024	OPEN	0	0	1	3
A	NULL	1/24/2024	NULL	1/24/2024	CLOSED	0	1	0	4
B	1/20/2024	NULL	NULL	1/20/2024	OPEN	1	0	1	1
B	NULL	1/15/2024	NULL	1/15/2024	CLOSED	0	1	0	2
C	1/30/2024	NULL	NULL	1/30/2024	OPEN	1	0	1	1
DimClaim
CLAIM	ADJUSTER	CAUSE
A	Anne	Theft
B	Charlie	Water
C	Charlie	Wind
Expected Result
MonthEnded	New in Month	Closed in Month	Pending at month end
1/31/2024	3	2	1

A few ideas I have had are:

1. summing the FactClaimStatus[CLOSED COUNTER] for the max RN in a month , if the CLOSED COUNTER for a claim in the month is 1 then it means it closed that month
2. counting the number of times SUFFIX STATUS = CLOSED for each claim during a month based on the max RN

This measre would then be followed by a YTD version.

Thanks

[Ashish_Mathur]

Hi,

Are all the columns in the Fact table source data table columns or are any of them calculated?  if yes, then which are they?  Also, for cliam B, how can the date closed be before the date opened?

[BuffaloAnalyst]

Thanks for asking Ashish_Mathur, all the columns in the Fact table are columns, nothing is calculated. And for Claim B, that was a typo, I was making up some fake daa and did a bad job, the claim can not close before it opens. 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[BuffaloAnalyst]

Thanks, I appreciate your effort, I tried to apply your logic to a larger data set and the results weren't as expected.

Here is one more claim to add, claim D closed on the same day it opened in January, and then reopened in April and closed again that same day. 

CLAIM	DATE OPENED	DATE CLOSED	DATE REOPENED	TRANS DATE	SUFFIX STATUS	NEW COUNTER	CLOSED COUNTER	PENDING COUNTER	RN
D	1/12/2024			1/12/2024	OPEN	1	0	1	1
D		1/12/2024		1/12/2024	CLOSED	0	1	0	2
D			4/16/2024	4/16/2024	OPEN	0	0	1	3
D		4/16/2024		4/16/2024	CLOSED	0	1	0	4

When I added the data to the table in your PBI model, I expected January to say 3 and April 1, as during January three claims were closed as of that month end, and then in April claim D reopened and then closed again.

I'm not sure if the claim closing on the same day it opened is causing issues with the Last Status measure you created, I think for each claim it needs to look for the max RN for each month and if the status shows as CLOSED for the max RN in a month then it counts it.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[BuffaloAnalyst]

Thanks, that partially worked. Claim D was correctly counted as closed in January and April, however for claim D, if you add one more row of data as the claim reopening in April after it closed then the measure did not work as expected:

CLAIM	DATE OPENED	DATE CLOSED	DATE REOPENED	TRANS DATE	SUFFIX STATUS	NEW COUNTER	CLOSED COUNTER	PENDING COUNTER	RN
D	1/12/2024			1/12/2024	OPEN	1	0	1	1
D		1/12/2024		1/12/2024	CLOSED	0	1	0	2
D			4/16/2024	4/16/2024	OPEN	0	0	1	3
D		4/16/2024		4/16/2024	CLOSED	0	1	0	4
D			4/18/2024	4/18/2024	OPEN	0	0	1	5

I think that the "Last Status" measure is not working as intended, as the max RN for April is now 5, and for that record the SUFFIX STATUS = OPEN, however the "Last Status" measure is showing CLOSED when I added that extra line of data when claim D is now open again. 

I see that within the "Last Status" measure you are looking for MIN(Data[SUFFIX STATUS]), which means alphabetically CLOSED always comes before OPEN. I also don't understand why FILTER(VALUES(Data[RN]),Data[RN] = [Last RN value]) is not returning only RN = 5 for Claim D in April. 

[Ashish_Mathur · Accepted solution]

PBI file attached.

[BuffaloAnalyst]

That is great, thank you! Are you able to explain why it has to be two measures instead of trying to write it as one measure like this?

ClosedClaims =

 VAR _LastStatus =

CALCULATE (

    MIN(Data[SUFFIX STATUS])

    ,FILTER(VALUES(Data[RN]),Data[RN]=max(Data[RN]))

    )

RETURN

    COUNTROWS(FILTER(VALUES(Data[CLAIM]),_LastStatus="Closed"))

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

PBI file attached.

[rajendraongole1]

Hi @BuffaloAnalyst - calculate the number of insurance claims closed during a month, ensuring that each claim is counted only once even if it closes and reopens multiple times within the same month

NewInMonth =
CALCULATE(
DISTINCTCOUNT(FactClaimStatus[CLAIM]),
FactClaimStatus[NEW COUNTER] = 1,
FactClaimStatus[MonthEnded] = MAX(FactClaimStatus[MonthEnded])
)

Claims Closed in Month

ClosedInMonth =
CALCULATE(
DISTINCTCOUNT(FactClaimStatus[CLAIM]),
FactClaimStatus[SUFFIX STATUS] = "CLOSED",
FactClaimStatus[RN] =
CALCULATE(
MAX(FactClaimStatus[RN]),
ALLEXCEPT(FactClaimStatus, FactClaimStatus[CLAIM]),
FactClaimStatus[MonthEnded] = MAX(FactClaimStatus[MonthEnded])
)
)

calcualte another measure to get the pending month end value

PendingAtMonthEnd =
CALCULATE(
DISTINCTCOUNT(FactClaimStatus[CLAIM]),
FactClaimStatus[PENDING COUNTER] = 1,
FactClaimStatus[RN] =
CALCULATE(
MAX(FactClaimStatus[RN]),
ALLEXCEPT(FactClaimStatus, FactClaimStatus[CLAIM]),
FactClaimStatus[MonthEnded] = MAX(FactClaimStatus[MonthEnded])
)
)

Hope this works 

[BuffaloAnalyst]

Thanks  rajendraongole1, I'm also using a date table, DimDate with two columns, date (FullDateAlternateKey) and last day of month ([LastDayOfMonth]). I joined FactClaimStatus to Dimdate from FactClaimStatus[TRANS DATE] to DimDate[FullDateAlternateKey]. Would that change the last line of the measure "Claims Closed in Month" as I don't have a column called FactClaimStatus[MonthEnded].