Forum Discussion

IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

Power Query: Date/Time Falls Between Business Hours

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

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

5 Replies

    • IamTDR's avatar
      IamTDR
      Icon for Responsive Resident rankResponsive Resident
      6 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      IamTDR
      Icon for Responsive Resident rankResponsive Resident
      6 years ago

      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