Highlight whether the follow up was completed or not based on Next follow up date

[Anonymous]

Hello community members,

Seeking your help and advice in achieving the below results. 

I have this dataset (attached image). It is basically a follow-up table. Where once you complete a follow up then you create an entry and in that entry you also indicate wheater the next follow-up is required or not and if required then you enter "Next follow-up date" and there could be multiple follow-ups per case (related to case table using case id). 

Now the ask is to highlight entries in the power bi dashboard where the follow-up was due and wasn't completed. There is no indication in the dataset saying whether the follow-up was completed or not. 

Now to achieve that I was thinking of designing a measure that would look at first whether the "Next follow up date" is present or not and if it's present, It would look at the future entries for the same case (using case Id) to see if the new row was created or not. if the row is created then it shall indicate that the follow-up was created but if there's no future entry and "Next follow up date" is past due then It would Indicate follow-up is not completed.

Please let me know if you need further explanation.

Thanks in advance!

[MFelix]

Hi @Anonymous ,

In your example you don't have duplicated ID to get the needs or not follow up I added two lines to check the formula validation try the folllowing measure:

Follow up Status = 
IF (
  NOT ( ISBLANK ( SELECTEDVALUE ( 'Table'[NExt Follow up] ) ) ),
    IF (
        COUNTROWS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Case ID] = SELECTEDVALUE ( 'Table'[Case ID] )
                    && 'Table'[F/U Date] >= SELECTEDVALUE ( 'Table'[NExt Follow up] )
            )
        ) + 0 = 0,
        "Follow UP Incomplete",
        "Completed"
    ),
"Does not need Follow Up")

If you need to have this without the context of the follow up date then this measure need to be change please tell me if you need it.

[Anonymous]

Hi Felix,

This works, however, I also needed to know if the follow-up is overdue (Past today's date) or If follow up due but it's overdue (Follow up is required but it is still within target date).
Can you please advise how can I implement this?

I did something similar using a calculated column where I'm looking for any future entries if the follow-up was required and comparing that date with today's (or Selected date) to see if it's overdue or not but I need to achieve this using a measure.

Here's my code.

Follow up Completed Date = 
VAR Index = [Index] + 1
VAR _calc =
    CALCULATE (
        MAX ( 'OccHealth_Follow ups_Test'[F/U Date].[Date] ),
        FILTER (
            'OccHealth_Follow ups_Test',
            'OccHealth_Follow ups_Test'[Case ID.lookupValue]
                = EARLIER ( 'OccHealth_Follow ups_Test'[Case ID.lookupValue] )
                && 'OccHealth_Follow ups_Test'[Index] = Index
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( 'OccHealth_Follow ups_Test'[Next Follow up Date] ) ),
        _calc,
        BLANK ()
    )

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following code:

Follow up Status =
VAR FollowUpCalculation =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Case ID] = SELECTEDVALUE ( 'Table'[Case ID] )
                && 'Table'[F/U Date] >= MAX ( 'Table'[NExt Follow up] )
        )
    ) + 0
RETURN
    IF (
        NOT ( ISBLANK ( SELECTEDVALUE ( 'Table'[NExt Follow up] ) ) ),
        SWITCH (
            TRUE (),
            FollowUpCalculation = 0
                && SELECTEDVALUE ( 'Table'[NExt Follow up] ) - TODAY () < 0, "Follow Overdue",
            FollowUpCalculation = 0
                && SELECTEDVALUE ( 'Table'[NExt Follow up] ) - TODAY () > 0, "Follow Due",
            FollowUpCalculation = 0, "Follow up Incomplete",
            "Completed"
        ),
        "Does not need Follow Up"
    )