Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
I appreciate your assistance with this matter
Regards
Kate
Solved! Go to 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
Proud to be a Super User!
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
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?
Proud to be a Super User!
Or what if a case spans several weeks with weeks that it was and wasn't on hold?
Proud to be a Super User!
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
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.
Proud to be a Super User!
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
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
Proud to be a Super User!
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
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.
Proud to be a Super User!
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
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
61 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |