Forum Discussion

kateVH's avatar
kateVH
Regular Visitor
2 years ago
Solved

weekly chart to count the specific status

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

Need Help
  • danextian's avatar
    danextian
    2 years ago

    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

13 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    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.

  • kateVH's avatar
    kateVH
    Regular Visitor
    2 years ago

    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's avatar
      danextian
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        kateVH
        Regular Visitor
        2 years ago

        Hi danextian 

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

         

        Thanks

        Kate