How to get a unique count based on dates and policy status

[aaquino]

Good morning everyone,

I've never used this forum before, but I've been struggling with solving this problem on my own. I'll try to be specific and hopefully someone out there might be able to help me.

I have a table that contains policies, and each policy goes through different stages in a given period. In the example that I attached, this policy has gone from Pending -> Approved -> Issued. What I want to do is to get a unique count of policies based on the selected filter that I have above. In the example it is 11/16/2020, and this date has to be in between the ROWEFFECTIVITYEBEGINDATETIME and ROWEFFECTIVITYENDDATETIME. If we look at this example this occurs several times, but the problem is that it occurs once during Approved and once during Issued. This would give me a count for both Issued and Approved for the same policy. I only want a count for Issued since thats the last stage of a policy. Additional information, if this occurs then Pending would be lowest, Approved in the middle and Issued Highest. If this occurs for both Pending and Approved then only Approved would be counted. If this happens for both Pending and Issued then only issued would be captured. I hope this is making sense. I originally tried getting a count using this measure: 

Unique Policies = CALCULATE(DISTINCTCOUNT('Pending Business'[POLICYNUMBER]),DATEVALUE('Pending Business'[ROWEFFECTIVITYBEGINDATETIME]) <= DATEVALUE(SELECTEDVALUE('Dim Calendar Date'[CALENDAR_DATE])),DATEVALUE('Pending Business'[ROWEFFECTIVITYENDDATETIME]) >= DATEVALUE(SELECTEDVALUE('Dim Calendar Date'[CALENDAR_DATE]))), but it did not work because it would display a correct unique count of policies, but not a unique count per POLICYSTATUSDESC. Looking forward to everyone's feedback! Thank you.

[v-yanjiang-msft]

Hi @aaquino ,

I'm not quite sure what the conditions are for the count.

For example the below sample I created, if I select 2022/1/8 in the slicer. For PolicyNumber1, the status of Issued match the date, it certainly should be in count. For PolicyNumber2, the status of Approved match the date but Issued not match, does it should be in count? For PolicyNumber3, the status of Approved match the date and have no Issued status, does it should be in count. Please explain more about the condition.

Best Regards,
Community Support Team _ kalyj

[aaquino]

What I am looking for is a count of policies for the given POLICYSTATUSDESC. This DAX code gives me a count for both Approved and Issued

Unique Policies = CALCULATE(DISTINCTCOUNT('Pending Business'[POLICYNUMBER]),DATEVALUE('Pending Business'[ROWEFFECTIVITYBEGINDATETIME]) <= DATEVALUE(SELECTEDVALUE('Dim Calendar Date'[CALENDAR_DATE])),DATEVALUE('Pending Business'[ROWEFFECTIVITYENDDATETIME]) >= DATEVALUE(SELECTEDVALUE('Dim Calendar Date'[CALENDAR_DATE]))), but I only need a count for Issued because that is the highest priority. I don't want issued to be counted/displayed.

[Kaly · Accepted solution]

Hi @aaquino ,

That is to say, you want to count the PolicyNumber which Issued date contains the given date.

In the below sample, if the selected calendar date is 2022/1/8, the count result should be 1, as only the Issued status of Policy Number1 contains the selected date 2022/1/8.

If I understand correctly, here's my solution. Create a measure.

Unique Policies =
CALCULATE (
    DISTINCTCOUNT ( 'Pending Business'[POLICYNUMBER] ),
    FILTER (
        'Pending Business',
        'Pending Business'[POLICYSTATUSDESC] = "Issued"
            && 'Pending Business'[ROWEFFECTIVITYBEGINDATETIME]
                <= SELECTEDVALUE ( 'Dim Calendar Date'[CALENDAR_DATE] )
            && 'Pending Business'[ROWEFFECTIVITYENDDATETIME]
                >= SELECTEDVALUE ( 'Dim Calendar Date'[CALENDAR_DATE] )
    )
)

Get the correct result.

Best Regards,

Kaly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.