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 example, my slicer is between 1/1/2021 and 1/3/2021. 
I have the following Policy:

Reported Start DatePolicyStatus
01/01/2021123ABCOpen
01/02/2021123ABCOpen
01/03/2021123ABCOpen
01/04/2021123ABCClose

 

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

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.

4 Replies

  • 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?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    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.

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

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

       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!!