Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I Have Cases are Created by Customers duing Non Business Hours, When The Agents login during business hours they work on case.
I need to Calculate the TAT. From the Case Created Date - Case Touched by Agent.
Business Hours Start | 8:30 AM |
Business Hours End | 6:00 PM |
The Problem is that the Customers can Create cases any time. So cases can be created before or after the Business hours. We can't penalized the Agents since the cases came before they loggin and have a higher TAT.
I want to convert all the case Cases to which came in during the non business Hours to be converted to business hours..
Created Date to Date Modified
I have Come Conditions.below.
If The Created before 8:30 AM the same Day . The Date Modified should be change to the same date 8:30 AM |
If The Created After 6:00 PM . The Date Modified should be change to the Next date 8:30 AM |
If a Case is Created After 6PM Friday then the Date modified should be changed to Monday 8:30AM |
If Case is Created on a Weekend (SAT & SUN), Then the Date Modified Should be Monday 8:30AM |
My Sample Table
Case No | Created On | WeeK Day | Date Moddified | Week Day |
1 | 01/01/2019 09:22:04 | 2 | 1/1/2019 9:22 | 2 |
2 | 01/01/2019 14:10:52 | 2 | 1/1/2019 14:10 | 2 |
3 | 01/01/2019 19:11:15 | 2 | 1/2/2019 8:00 | 3 |
4 | 01/01/2020 06:26:34 | 3 | 1/1/2020 8:00 | 3 |
5 | 11/02/2018 09:21:52 | 5 | 11/5/2018 8:30 | 1 |
6 | 10/26/2019 23:22:58 | 6 | 10/28/2019 8:30 | 1 |
7 | 10/27/2019 23:22:10 | 7 | 10/28/2019 8:20 | 1 |
8 | 10/28/2019 05:53:40 | 1 | 10/28/2019 5:53 | 1 |
9 | 10/28/2019 07:08:43 | 1 | 10/28/2019 8:00 | 1 |
10 | 10/28/2019 18:51:15 | 1 | 10/29/2019 8:00 | 2 |
11 | 10/28/2019 22:34:08 | 1 | 10/29/2019 8:00 | 2 |
I Would like to have a DAX Calculated Column, Its becauseI have Created a Calculated Column in Dax Converting UTC time To CET time. with CET daylight timings
Created Date CET = VAR currentLineYear = YEAR ( [Case Created Date] ) VAR LastSunOct = MAXX ( FILTER ( CALENDARAUTO (), YEAR ( [Date] ) = currentLineYear && MONTH ( [Date] ) = 10 && WEEKDAY ( [Date], 2 ) = 7 && HOUR([Date] <=2 ) ), [Date] ) VAR LastSunMar = MAXX ( FILTER ( CALENDARAUTO (), YEAR ( [date] ) = currentLineYear && MONTH ( [Date] ) = 3 && WEEKDAY ( [Date], 2 ) = 7 && HOUR ([Date] >=3 ) ), [Date] ) RETURN IF ( [Case Created Date] >= LastSunOct || [Case Created Date] <= LastSunMar, [Case Created Date] + TIME ( 1, 0, 0 ), [Case Created Date] + TIME ( 2, 0, 0 ) ) |
If you need more Info please let me know
Regards,
Charles Thangaraj
Solved! Go to Solution.
Hi @Technowolf ,
Try to create a calculated column like so:
Date Modified =
VAR CreatedTime =
TIME ( HOUR ( 'Table'[Created On] ), MINUTE ( 'Table'[Created On] ), SECOND ( 'Table'[Created On] ) )
VAR CreatedDate = 'Table'[Created On].[Date]
VAR BusinessStart =
TIME ( 8, 30, 0 )
VAR BusinessEnd =
TIME ( 18, 0, 0 )
RETURN
SWITCH (
TRUE (),
'Table'[WeeK Day] IN { 1, 2, 3, 4 }, SWITCH (
TRUE (),
CreatedTime >= BusinessStart
&& CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime,
CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart,
CreatedTime > BusinessEnd, DATEADD ( 'Table'[Created On].[Date], 1, DAY ) & " " & BusinessStart
),
'Table'[WeeK Day] = 5, SWITCH (
TRUE (),
CreatedTime >= BusinessStart
&& CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime,
CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart,
CreatedTime > BusinessEnd, DATEADD ( 'Table'[Created On].[Date], 3, DAY ) & " " & BusinessStart
),
'Table'[WeeK Day] = 6, DATEADD ( 'Table'[Created On].[Date], 2, DAY ) & " " & BusinessStart,
'Table'[WeeK Day] = 7, DATEADD ( 'Table'[Created On].[Date], 1, DAY ) & " " & BusinessStart
)
Then, change data type.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Technowolf ,
Try to create a calculated column like so:
Date Modified =
VAR CreatedTime =
TIME ( HOUR ( 'Table'[Created On] ), MINUTE ( 'Table'[Created On] ), SECOND ( 'Table'[Created On] ) )
VAR CreatedDate = 'Table'[Created On].[Date]
VAR BusinessStart =
TIME ( 8, 30, 0 )
VAR BusinessEnd =
TIME ( 18, 0, 0 )
RETURN
SWITCH (
TRUE (),
'Table'[WeeK Day] IN { 1, 2, 3, 4 }, SWITCH (
TRUE (),
CreatedTime >= BusinessStart
&& CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime,
CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart,
CreatedTime > BusinessEnd, DATEADD ( 'Table'[Created On].[Date], 1, DAY ) & " " & BusinessStart
),
'Table'[WeeK Day] = 5, SWITCH (
TRUE (),
CreatedTime >= BusinessStart
&& CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime,
CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart,
CreatedTime > BusinessEnd, DATEADD ( 'Table'[Created On].[Date], 3, DAY ) & " " & BusinessStart
),
'Table'[WeeK Day] = 6, DATEADD ( 'Table'[Created On].[Date], 2, DAY ) & " " & BusinessStart,
'Table'[WeeK Day] = 7, DATEADD ( 'Table'[Created On].[Date], 1, DAY ) & " " & BusinessStart
)
Then, change data type.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey
Your DAX solution works like a Charm, I was trying to Calculate TAT on networking Days and hours. I got a solution in DAX but its not working with Calculated column. So I am try to move all my calculation powerquery.(https://community.powerbi.com/t5/Desktop/TAT-of-Working-Business-Hours-and-Excluding-Weekend/m-p/104...),
Can you help convert the same logic in Power Query.
Regards,
Charles Thagaraj
Hi
Thanks for your Help. This is exactly what i was looking for.
I am getting error in my output.ADX Cases'[Created Date CET].[Date]
Date Modified = VAR CreatedTime = TIME ( HOUR ( 'ADX Cases'[Created Date CET]), MINUTE ( 'ADX Cases'[Created Date CET]), SECOND ( 'ADX Cases'[Created Date CET]) ) VAR CreatedDate = 'ADX Cases'[Created Date CET].[Date] VAR BusinessStart = TIME ( 8, 30, 0 ) VAR BusinessEnd = TIME ( 18, 0, 0 ) RETURN SWITCH ( TRUE (), 'ADX Cases'[Created CET WeekDay] IN { 1, 2, 3, 4 }, SWITCH ( TRUE (), CreatedTime >= BusinessStart && CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime, CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart, CreatedTime > BusinessEnd, DATEADD ( 'ADX Cases'[Created Date CET].[Date], 1, DAY ) & " " & BusinessStart ), 'ADX Cases'[Created CET WeekDay] = 5, SWITCH ( TRUE (), CreatedTime >= BusinessStart && CreatedTime <= BusinessEnd, CreatedDate & " " & CreatedTime, CreatedTime < BusinessStart, CreatedDate & " " & BusinessStart, CreatedTime > BusinessEnd, DATEADD ( 'ADX Cases'[Created Date CET].[Date], 3, DAY ) & " " & BusinessStart ), 'ADX Cases'[Created CET WeekDay] = 6, DATEADD ( 'ADX Cases'[Created Date CET].[Date], 2, DAY ) & " " & BusinessStart, 'ADX Cases'[Created CET WeekDay] = 7, DATEADD ( 'ADX Cases'[Created Date CET].[Date], 1, DAY ) & " " & BusinessStart ) |
Hi @Technowolf ,
It works well on my side. Please try these operations:
1. Make sure the data type of 'ADX Cases'[Created Date CET] is Date / Time.
2. You can try to delete this part and re-enter. Sometimes, this operation works.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
I was able to solve this by enabling time intelligence in settings, that was disabled thats why it was not working. Your Solution worked as a charm, Thanks a Ton.
Regards,
Charles Thangaraj
Hi @Technowolf ,
Try this code for a calcuted column:
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |