Return latest policy status

[dexter2424]

Hi All,

I need help in PowerBI because cannot figure out how to solve this issue.

I would like to return the latest policy status up to the maximum selected date by policy number.

for example, my slicer is between 1/1/2021 and 1/3/2021. 
I have the following Policy:

Reported Start Date	Policy	Status
01/01/2021	123ABC	Open
01/02/2021	123ABC	Open
01/03/2021	123ABC	Open
01/04/2021	123ABC	Close

I need two cards, one with Open policies and another with Closed policies. Once I set the slicer up to the 1st of March 2021, I would like to see the cards like this:
Open Policy: 1, Closed Policy 0

Once I set the slicer up to the 1st of April 2021, I would like to see the following:
Open Policy: 0, Closed Policy 1

So in summary, I would like to count a policy only once and put it in the open or closed policy bucket. 

I have created a calendar table for the slicer and created some measures, but no luck 😕 If you can give me any advice I would be really thankful.

Regards,

Denes

[v-yalanwu-msft · Accepted solution]

Hi, @dexter2424 

You could create two measures by the following formula:

Open =
VAR _last =
    CALCULATE (
        COUNT ( [Policy] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Reported Start Date] = MAX ( [Reported Start Date] )
                && [Status] = "Open"))
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( [Policy] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Status] = "Open" ))
RETURN
    IF ( _last = BLANK (), IF ( _count = 1, 0, -1 ), _last )

Close =
VAR _last =
    CALCULATE (
        COUNT ( [Policy] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Reported Start Date] = MAX ( [Reported Start Date] )
                && [Status] = "Close"))
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( [Policy] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Status] = "Close" )
    )
RETURN
    IF ( _last = BLANK (), IF ( _count = 1, 0, -1 ), _last )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dexter2424]

Hi v-yalanwu-msft, thank you for your solution, there is only 1 exception. 
It shows the exact date matching. I would like to see the latest one. So using the same example above:

Reported Start Date	Policy	Status
01/01/2021	123ABC	Open
01/02/2021	123ABC	Close
01/03/2021	123ABC	Open
01/04/2021	123ABC	Close
01/05/2021	123ABC	Close

Once I select 01/06/2021 it shows a blank result, because there is no result on 01/06/2021. In this case, I would like to see the latest non-blank or latest existing value. so the Closed on 01/05/2021. 
I have tried to modify your query from 

 [Reported Start Date] = MAX ( [Reported Start Date]

to:

 [Reported Start Date] <= MAX ( [Reported Start Date]

 but it's also wrong because the query looks for the latest "Open" and "Closed" policies, so count once as open and count once as closed. 
So the result with this modification is: 1 Open (1/3/2021) and 1 Closed (1/5/2021). 

I would like to see: 0 Open and 1 Closed, because the slider's date is 1/5/2021 and the latest ABC123 Policy was on 1/5/2021, and it's status was Closed, so wanna count as 1 Closed

Thank you very much!!

[amitchandak]

@dexter2424 , with an independent date table for slicer

Open  =
var _max = maxx(allselected('Date'), Date[Date])

return
calculate(distinctCOUNT(Table[Policy]), filter(Table, Table[Status]="Open" && Table[Reported Start Date] <=_max)) -
calculate(distinctCOUNT(Table[Policy]), filter(Table, Table[Status]="Close" && Table[Reported Start Date] <=_max))

close  =

new measure =
var _max = maxx(allselected('Date'), Date[Date])

return
calculate(distinctCOUNT(Table[Policy]), filter(Table, Table[Status]="Close" && Table[Reported Start Date] <=_max))

[dexter2424]

Thanks Amitchandak,

The solution is mostly right, except for some things. I need to pick up the latest status in the selected period. 
So for example:

Reported Start Date	Policy	Status
01/01/2021	123ABC	Open
01/02/2021	123ABC	Close
01/03/2021	123ABC	Open
01/04/2021	123ABC	Close
01/05/2021	123ABC	Close

When I select the period between 01/01/2021 and 01/02/2021, it says 1 open and 1 closed it's 0 open and 1 closed. But the period in 01/02/2021 and 01/03/2021, I have 1 open and 1 closed as well, but I need to count it as open because open is the latest one. So I always need to count the latest status. The other thing, if I have only closed ones like in between 01/04/2021 and 01/05/2021, the open count will show -1, because 0 open and 1 closed, it's 0-1=-1

😕
do you have any idea?