weekly chart to count the specific status

[kateVH]

Dear all,

I hope you’re doing well!

I need your assistance in resolving an issue. Specifically, I’d like to create a weekly chart to track cases that are on hold at the end of the week, specifically on the 5th day (Friday).

Here are the scenarios I’d like your help with:

1st

In the first scenario, if the status is ‘On Hold’ on Tuesday but changes to a different status within the same week, it should not be included in the count.

2nd scenario

In the second scenario, a case may have been on hold multiple times within the same week, but if its final status at the end of the week is ‘On Hold,’ it should be counted for that week.

3rd scenario

In the third scenario, if a case has been on hold during the same week (week 46) and remains ‘On Hold’ in the subsequent week (week 47), it should be counted for both weeks (46 and 47).

Could you please help me here?

I have attached the file below

Link to the PBIX file 

I appreciate your assistance with this matter

Regards

Kate

[kateVH]

Hello @danextian 

Thanks for the reply,

Actually, the case was On Hold in week 46 and moved to different status in the week 47 and after that it is again in On Hold, could you please check it again? 🙂

Thanks

Kate

[danextian]

What if a case was not on hold in the in week 1 but became so in weeks 2 and 3, which weeks should the count be included in?

[kateVH]

Hi @danextian 

then it should included in a count for the week 2 and 3 🙂

Thanks

Kate

[danextian]

Or what if a case spans several weeks with weeks that it was and wasn't on hold?

[kateVH]

Hi @danextian 

Let's say if the case was set to On hold on 14/11/2023 12:41 PM(week 47) and it is still on hold today(14/12/2023) (week 50), Then it should be included in a count for week 47, 48, 49 and 50, that's being said, it should be counted until it changes to the different status 🙂

Thanks

Kate

[danextian]

You'll need to change your data model a bit, add a several columns and a measure. Given the logic provided, here's a sample calc column referencing several other columns to identify whether a case is to be counted within that week.

To Count = 
VAR __MAX_CREATED_DATETIME =
    CALCULATE (
        MAX ( 'Case history'[Created Date] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                && 'Case history'[Week Ending] = EARLIER ( 'Case history'[Week Ending] )
        )
    )
VAR __SCENARIO_2 =
    CALCULATE (
        MAX ( 'Case history'[New Value] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Created Date Only] = EARLIER ( 'Case history'[Week Ending] )
                && 'Case history'[Created Date] = __MAX_CREATED_DATETIME
        )
    ) = "On Hold"
VAR __SCENARIO_3 =
    VAR __CURRENT_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Week Ending] = EARLIER ( 'Case history'[Week Ending] )
                    && 'Case history'[New Value] = "On Hold"
            )
        )
    VAR __NEXT_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Week Ending]
                        = EARLIER ( 'Case history'[Week Ending] ) + 7
                    && 'Case history'[New Value] = "On Hold"
            )
        )
    VAR __PREV_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Week Ending]
                        = EARLIER ( 'Case history'[Week Ending] ) - 7
                    && 'Case history'[New Value] = "On Hold"
            )
        )
    RETURN
        ( NOT ( ISBLANK ( __CURRENT_WEEK ) ) && NOT ( ISBLANK ( __NEXT_WEEK ) ) )
            || NOT ( ISBLANK ( __CURRENT_WEEK ) ) && NOT ( ISBLANK ( __PREV_WEEK ) )
RETURN
    __SCENARIO_2 || __SCENARIO_3

Please see attached pbix for the details.

[kateVH]

Hi @danextian,

First of all thank you so much once again ❤️

Everything looks perfect but there is a small thing to be modified,

Regarding case number 116ba,

- The case was placed on hold in week 32 and at the end of the week 32 the case was in the status "In progress", so it shouldn't be included in the count for a week 32

-During week 33, despite the case being initially placed on hold, it subsequently advanced to the next status, which, in this instance, is closed. As a result, it was not on hold by the end of week 33 therefore, it should not be included in the count for week 33.

Could you please help me here? 🙂

Thank you in Advance

Regards

Kate

[danextian · Accepted solution]

Hi @kateVH ,

Try this:

To Count = 
VAR __MAX_CREATED_DATETIME =
    CALCULATE (
        MAX ( 'Case history'[Created Date] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                && 'Case history'[Week Ending] = EARLIER ( 'Case history'[Week Ending] )
        )
    )
VAR __LATEST_VALUE =
    CALCULATE (
        MAX ( 'Case history'[New Value] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                && 'Case history'[Created Date] = __MAX_CREATED_DATETIME
        )
    )
VAR __SCENARIO_2 =
    CALCULATE (
        MAX ( 'Case history'[New Value] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Created Date Only] = EARLIER ( 'Case history'[Week Ending] )
                && 'Case history'[Created Date] = __MAX_CREATED_DATETIME
        )
    ) = "On Hold"
VAR __SCENARIO_3 =
    VAR __CURRENT_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Week Ending] = EARLIER ( 'Case history'[Week Ending] )
                    && 'Case history'[New Value] = "On Hold"
            )
        )
    VAR __NEXT_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Week Ending]
                        = EARLIER ( 'Case history'[Week Ending] ) + 7
                    && 'Case history'[New Value] = "On Hold"
            )
        )
    VAR __PREV_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Week Ending]
                        = EARLIER ( 'Case history'[Week Ending] ) - 7
                    && 'Case history'[New Value] = "On Hold"
            )
        )
    RETURN
        (
            ( NOT ( ISBLANK ( __CURRENT_WEEK ) ) && NOT ( ISBLANK ( __NEXT_WEEK ) ) )
                || NOT ( ISBLANK ( __CURRENT_WEEK ) ) && NOT ( ISBLANK ( __PREV_WEEK ) )
        )
            && __LATEST_VALUE = "On Hold"
RETURN
    __SCENARIO_2 || __SCENARIO_3

[kateVH]

Hi @danextian 

Hope you are doing great,

I have got just one quick question, I was trying to find the count for the Status "In Progress" by changing the On Hold to In Progress in the above code, but it didn't work. Should I do something more to check the count for other statuses? 🙂

Could you please help me here?

Thank you in Advance

Regards

Kate

[danextian]

Hi @kateVH 

Try this:

To Count In Progress = 
VAR __MAX_CREATED_DATETIME =
    CALCULATE (
        MAX ( 'Case history'[Created Date] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                && 'Case history'[Weeknum] = EARLIER ( 'Case history'[Weeknum] )
        )
    )
VAR __LATEST_VALUE =
    CALCULATE (
        MAX ( 'Case history'[New Value] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                && 'Case history'[Created Date] = __MAX_CREATED_DATETIME
        )
    )
VAR __SCENARIO_2 =
    CALCULATE (
        MAX ( 'Case history'[New Value] ),
        FILTER (
            ALL ( 'Case history' ),
            'Case history'[Weeknum] = EARLIER ( 'Case history'[Weeknum] )
                && 'Case history'[Created Date] = __MAX_CREATED_DATETIME
        )
    ) = "In Progress"
VAR __SCENARIO_3 =
    VAR __CURRENT_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Weeknum] = EARLIER ( 'Case history'[Weeknum] )
                    && 'Case history'[New Value] = "In Progress"
            )
        )
    VAR __NEXT_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Weeknum]
                        = EARLIER ( 'Case history'[Weeknum] ) + 7
                    && 'Case history'[New Value] = "In Progress"
            )
        )
    VAR __PREV_WEEK =
        CALCULATE (
            COUNTROWS ( 'Case history' ),
            FILTER (
                ALL ( 'Case history' ),
                'Case history'[Case Number] = EARLIER ( 'Case history'[Case Number] )
                    && 'Case history'[Weeknum]
                        = EARLIER ( 'Case history'[Weeknum] ) - 7
                    && 'Case history'[New Value] = "In Progress"
            )
        )
    RETURN
        (
            ( NOT ( ISBLANK ( __CURRENT_WEEK ) ) && NOT ( ISBLANK ( __NEXT_WEEK ) ) )
                || NOT ( ISBLANK ( __CURRENT_WEEK ) ) && NOT ( ISBLANK ( __PREV_WEEK ) )
        )
            && __LATEST_VALUE = "In Progress"
RETURN
   __SCENARIO_2 || __SCENARIO_3

You might want to use this formula as for On Hold as the  previous one compares Max Created Date Only against Week Ending which isn't always true as the max date may be earlier than the actual week ending date.

[kateVH]

Hi @danextian,

All good 🙂

Thank you so much for your help once again

Regards

Kate

[kateVH]

OMG Thank you!!!! I will have a look at it and come back to you.

Thank you so much for your time and help ❤️

Regards

Kate

[danextian]

Hi @kateVH 

Your third scenario is confusing.  113ba  was on hold at the start of week 47 only.  It wasn't on hold in  week 46.