Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Technowolf
Helper II
Helper II

Converting Non Business Hours to Business Hours.

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 Start8:30 AM
Business Hours End6: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 NoCreated On      WeeK DayDate ModdifiedWeek Day
101/01/2019 09:22:0421/1/2019 9:222
201/01/2019 14:10:5221/1/2019 14:102
301/01/2019 19:11:1521/2/2019 8:003
401/01/2020 06:26:3431/1/2020 8:003
511/02/2018 09:21:52511/5/2018 8:301
610/26/2019 23:22:58610/28/2019 8:301
710/27/2019 23:22:10710/28/2019 8:201
810/28/2019 05:53:40110/28/2019 5:531
910/28/2019 07:08:43110/28/2019 8:001
1010/28/2019 18:51:15110/29/2019 8:002
1110/28/2019 22:34:08110/29/2019 8:002

 

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

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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
    )

 

mo.PNG

 

Then, change data type.

datetype.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Icey
Community Support
Community Support

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
    )

 

mo.PNG

 

Then, change data type.

datetype.gif

 

 

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 is where I am  getting error.
 
I just Remamed These Columns as per my data set.
 'Table'[Created On]    to   'ADX Cases'[Created Date CET]
'Table'[WeeK Day]   to  'ADX Cases'[Created CET WeekDay]
 'Table'[Created On].[Date]  to  ADX Cases'[Created Date CET].[Date]

Date Modified 2.png

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
)


I dont know where I am going wrong. Please help me
 
Regards,
Charles Thangaraj
 

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

camargos88
Community Champion
Community Champion



Hi @Technowolf ,

 

Try this code for a calcuted column:

 

Date Modified =
IF(TIME(HOUR(Tabela[Created On ]); MINUTE(Tabela[Created On ]); 00) < TIME(8;30;0);
FORMAT(Tabela[Created On ]; "yyyy-mm-dd") & " " & TIME(8;30;0);
IF(TIME(HOUR(Tabela[Created On ]); MINUTE(Tabela[Created On ]); 00) > TIME(18;0;0) && WEEKDAY(Tabela[Created On ]; 1) IN {1;2;3;4;5};
FORMAT(DATE(YEAR(Tabela[Created On ]); MONTH(Tabela[Created On ]); DAY(Tabela[Created On ]) + 1); "yyyy-mm-dd") & " " & TIME(8;30;0);
IF (TIME(HOUR(Tabela[Created On ]); MINUTE(Tabela[Created On ]); 00) > TIME(18;0;0) && WEEKDAY(Tabela[Created On ]; 1) = 6;
FORMAT(DATE(YEAR(Tabela[Created On ]); MONTH(Tabela[Created On ]); DAY(Tabela[Created On ])) + 3; "yyyy-mm-dd") & " " & TIME(8;30;0);
IF (TIME(HOUR(Tabela[Created On ]); MINUTE(Tabela[Created On ]); 00) > TIME(18;0;0) && WEEKDAY(Tabela[Created On ]; 1) = 7 ;
FORMAT(DATE(YEAR(Tabela[Created On ]); MONTH(Tabela[Created On ]); DAY(Tabela[Created On ])) + 2; "yyyy-mm-dd") & " " & TIME(8;30;0);
FORMAT(Tabela[Created On ]; "") ))))
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.