Forum Discussion

dexter2424's avatar
dexter2424
Icon for Helper I rankHelper I
5 years ago
Solved

Return latest policy status

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 examp...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    dexter2424
    Icon for Helper I rankHelper I
    5 years ago

    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 DatePolicyStatus
    01/01/2021123ABC Open
    01/02/2021123ABC Close
    01/03/2021123ABC Open
    01/04/2021123ABC Close
    01/05/2021123ABC 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?