Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 data
Case No | Created On | Created WeeK Day | Date Moddified | Modified Week Day |
1 | 1/1/2019 9:22 | 2 | 1/1/2019 9:22 | 2 |
2 | 1/1/2019 14:10 | 2 | 1/1/2019 14:10 | 2 |
3 | 1/1/2019 19:11 | 2 | 1/2/2019 8:30 | 3 |
4 | 1/1/2020 6:26 | 3 | 1/1/2020 8:30 | 3 |
5 | 11/2/2018 9:21 | 5 | 11/5/2018 8:30 | 1 |
6 | 10/26/2019 23:22 | 6 | 10/28/2019 8:30 | 1 |
7 | 10/27/2019 23:22 | 7 | 10/28/2019 8:20 | 1 |
8 | 10/28/2019 5:53 | 1 | 10/28/2019 5:53 | 1 |
9 | 10/28/2019 7:08 | 1 | 10/28/2019 8:30 | 1 |
10 | 10/28/2019 18:51 | 1 | 10/29/2019 8:30 | 2 |
11 | 10/28/2019 22:34 | 1 | 10/29/2019 8:30 | 2 |
I Would like to have a power query Calculated Column, I had this Dax but my Later steps in my report is not working
I did try to convert weekend to weekdays need.
if [CET_CreatedWeekDay] = 6 then Date.AddDays([CET_CreatedDateTime],2) else if [CET_CreatedWeekDay] = 7 then Date.AddDays([CET_CreatedDateTime],1) else [CET_CreatedDateTime] |
I have this working in Dax but need this is power Query.
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 ) |
Thanks in Advanced.
Regards,
Charles Thangaraj
Solved! Go to Solution.
Sure, I can help you with the function if you help me with some Kudos along the way, deal?
1. Create a blank query
2. Open it in the advanced editor
3. Delete all the text and copy this code:
(inputDateT_)=>
let
dayWeek_=Date.DayOfWeek(inputDateT_,Day.Monday)+1, time_=Time.From(inputDateT_),
date00_ = DateTime.From(DateTime.Date(inputDateT_)),
res_= if dayWeek_ = 6 or dayWeek_ = 7 or (dayWeek_ = 5 and time_ > #time(18,0,0))
then date00_ + #duration(8-dayWeek_,8,30,0)
else if time_ < #time(8,30,0) then date00_ + #duration(0,8,30,0)
else if time_ > #time(18,0,0) then date00_ + #duration(1,8,30,0)
else inputDateT_
in res_
4. Name the query with the name you want for the function, for instance "convertToBusinessHours". Now you have the function
5. In your table, add a custom column with the following code:
= convertToBusinessHours([CET_CreatedDateTime])
Note if you choose another function name you have to update the code above with that name
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Technowolf
Try this based on your example. It would probably be more convenient to have the code in the custome column a s a function. You can create the function from it if you prefer.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case No" = _t, #"Created on" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Created on", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each let dayWeek_=Date.DayOfWeek([Created on],Day.Monday)+1, time_=Time.From([Created on]), date00_ = DateTime.From(DateTime.Date([Created on])), res_= if dayWeek_ = 6 or dayWeek_ = 7 or (dayWeek_ = 5 and time_ > #time(18,0,0)) then date00_ + #duration(8-dayWeek_,8,30,0) else if time_ < #time(8,30,0) then date00_ + #duration(0,8,30,0) else if time_ > #time(18,0,0) then date00_ + #duration(1,8,30,0) else [Created on] in res_, type datetime)
in
#"Added Custom1"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi
Can you help me to create the function my acutual columns name are CET_CreatedDateTime and Case Id.
I really appreciate the help.
this is the part I dont understand.
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case No" = _t, #"Created on" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created on", type datetime}}),
Thanks in advance
Regards,
Charles Thangaraj
Sure, I can help you with the function if you help me with some Kudos along the way, deal?
1. Create a blank query
2. Open it in the advanced editor
3. Delete all the text and copy this code:
(inputDateT_)=>
let
dayWeek_=Date.DayOfWeek(inputDateT_,Day.Monday)+1, time_=Time.From(inputDateT_),
date00_ = DateTime.From(DateTime.Date(inputDateT_)),
res_= if dayWeek_ = 6 or dayWeek_ = 7 or (dayWeek_ = 5 and time_ > #time(18,0,0))
then date00_ + #duration(8-dayWeek_,8,30,0)
else if time_ < #time(8,30,0) then date00_ + #duration(0,8,30,0)
else if time_ > #time(18,0,0) then date00_ + #duration(1,8,30,0)
else inputDateT_
in res_
4. Name the query with the name you want for the function, for instance "convertToBusinessHours". Now you have the function
5. In your table, add a custom column with the following code:
= convertToBusinessHours([CET_CreatedDateTime])
Note if you choose another function name you have to update the code above with that name
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi
It worked like a charm, Thanks a Ton
Regards,
Charles Thangaraj
The piece you refer too (below) is not very important. It is just a piece of code created by the GUI when you enter data manually. Try it: go to "Enter data", enter some data, press Load and you'll see a query gets created with a similar piece of code in the first step. It is the info of the table you've created manually compressed
=
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case No" = _t, #"Created on" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Created on", type datetime}}),
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
If you create another blank query, and enter the code below (same way as when we created the function), you'll see the first step is the creation of your base table (I copied directly what you posted on "Enter data") then a data type change in the second step and a third step in which the custom column is created by using the function.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7LDcQwCEXRViLWkczDP0wrlvtvIzFOovFsz0VA7wQ6CQFBGO1oJkLj7CS/imRg57hxM8A5fSx8FJPimqfeOKd1rl7DZTIHKWuLxPdmfUL9D/oEXSFbju5t92qs7vezW4BaXreBvYhYTDTGBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case Id" = _t, CET_CreatedDateTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CET_CreatedDateTime", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each convertToBusinessHours([CET_CreatedDateTime]))
in
#"Added Custom1"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
17 | |
11 | |
9 | |
8 |
User | Count |
---|---|
42 | |
24 | |
21 | |
13 | |
12 |