Power Query: Date/Time Falls Between Business Hours

[IamTDR]

Hi
I'm struggling to come up with something in power query that will show the following.

I have a records that have a 'Created At' field, which is a date/time field.  I also have a '1st Response Time' field, date/time.

The trouble is I need to tell if this '1st Response Time' field falls within the following windows.

- Monday through Thur between 9am to 3pm, Friday 9am to 10:30am

The goal is to look at the 'Created At' field, and see if the first response is within 2 hours...but need to make sure that the 'Created At' field is within business hours.

I'm really stumped here.  Any advice would be appreciated

Thanks in advance

[v-kelly-msft · Accepted solution]

Hi @IamTDR ，

First go to query editor> split columns :Split the date/time columns into 2 columns: date and time,as you see below:

Then create a calculated column to get the weekday of the field "create at"：

Weekday = FORMAT(WEEKDAY('Table (2)'[Create at.1],1),"DDDD")

Finally create 2 measures as below:

is within 2 hours = 
var a=DATEDIFF(MAX('Table (2)'[Create at.2]),MAX('Table (2)'[the first response.2]),MINUTE)/60 Return
IF(a<=2,1,0)

is within business hours = IF(MAX('Table (2)'[Weekday]) in FILTERS('Table'[Weekday ])&&MAX('Table (2)'[Create at.2])>=MAX('Table'[Working hour start ])&&MAX('Table (2)'[the first response.2])<=MAX('Table'[Working hour end]),1,0)

And you will see:

For the related .pbix file ,pls click here.

 
Best Regards,
Kelly

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

[IamTDR]

Thanks so much!  This looks like this will fit my need.

Also I appreicate the attached PBIX file as it will help me seee the steps and learn.

Thanks again

[Greg_Deckler]

I believe that the decimal portion of the date/time value is the time part. So, you could remove the integer portion and * by 24 to get the hour.

For day of week: https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek

[IamTDR]

Is there a way for me to create a field in this table that would later allow me to pivot on whether the 2 hour window has been fulfilled?

Should I be researching DateDiff for this?  

[Greg_Deckler]

Sorry, you asked about Power Query. DATEDIFF is DAX. Personally, Power Query's date time functions are fairly terrible to work with so DAX may not be a bad way to go. Yes, you could use DATEDIFF to get the number of hours between two dates very easily. It will be more of a pain in Power Query.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.