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.

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