Forum Discussion

kmcardle's avatar
kmcardle
Icon for Helper I rankHelper I
4 years ago
Solved

DaxTest11 = DATEDIFF('table'[Date/Time Opened],'table'[First Response],MINUTE) returns odd results

I am trying to return the number of minutes it takes to respond to a case.  I am using the formula: DaxTest11 = DATEDIFF('table'[Date/Time Opened],'table'[First Response],MINUTE) returns odd results in a new column I added to the dataset.

Data Example:

Date/Time                          First                                                                 Hand

Opened                              Response                                DaxTest11        Calculation

10/6/2021 11:53:00 AM     10/6/2021 12:04:00 PM                11                  11

 6/2/2021 11:56:00 AM       6/2/2021 12:18:00 PM                  22                  22

 3/2/2021 4:00:00 PM         3/3/2021 7:58:00 AM                2874                958

 

I have a ton of data here and this calculation works well for most but not all the data.  The inconsistency is what is odd and I cannot seem to understand what is wrong.  I am fairly new to Power BI and DAX so I am out of my depth with this.  I could really use some help as I am on my 2nd week of trying to figure this out on my own. Below is the About info from Power BI Desktop

 

Release: December 2021

Product Version: 2.100.1401.0 (21.12) (x64)

OS Version: Microsoft Windows NT 10.0.19043.0 (x64 en-US)

CLR Version: 4.7 or later [Release Number = 528372]

Model Default Mode: Import

Model Version: PowerBI_V3

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Try this:

     

    My Time to Respond =

    VAR StartHour = HOUR(SRInsights-SupportCases'[Date/Time Opened]) VAR EndHour = HOUR(SRInsights-SupportCases'[First Response Date]) RETURN 

    IF (StartHour > 18 && EndHour < 8, [Whatever formula you need when after 6PM && before 8AM],

    (((DATEDIFF('SRInsights-SupportCases'[Date/Time Opened], 'SRInsights-SupportCases'[First Response Date], MINUTE))))

    --Nate

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Try this:

     

    My Time to Respond =

    VAR StartHour = HOUR(SRInsights-SupportCases'[Date/Time Opened]) VAR EndHour = HOUR(SRInsights-SupportCases'[First Response Date]) RETURN 

    IF (StartHour > 18 && EndHour < 8, [Whatever formula you need when after 6PM && before 8AM],

    (((DATEDIFF('SRInsights-SupportCases'[Date/Time Opened], 'SRInsights-SupportCases'[First Response Date], MINUTE))))

    --Nate

    • kmcardle's avatar
      kmcardle
      Icon for Helper I rankHelper I
      4 years ago

      Nate - Thank you.  This looks very promising.  I will work through all the possible conditions and then give this a try.  You're awesome.  I am so lost with DEX and Power Query.

       

      Thanks

      Kevin

      • kmcardle's avatar
        kmcardle
        Icon for Helper I rankHelper I
        4 years ago

        Nate, I am having a difficult time trying to understand how to use VAR.

        I have tried to use the same in a column as well as a measure but I always get syntax errors.

        Would you have a link to a tutorial I can follow to understand what I am doing wrong?

        I apologize for not having a better understanding of Power BI. 

        I appreciate you help for sure!!  Thank you

  • tJohn's avatar
    tJohn
    Regular Visitor
    4 years ago

    Hi , 

    Please give a try following these steps :

    Opened_Target = FORMAT('DataExample'[Opened],"dd/MM/yyyy") & " " & TIMEVALUE("18:00:00")
    Response_Target = FORMAT('DataExample'[Response],"dd/MM/yyyy") & " " & TIMEVALUE("08:00:00")
    don't forget to convert from Text  to DateTime (from Tool Tab Column)
     
    Then create these 2 columns for time comparison
    - Opened after 6 PM = SWITCH(TRUE(),
    DataExample[Opened]>= DataExample[Opened_Target], "Opened after 6 PM" ,BLANK())
    - Response after 8 AM = SWITCH(TRUE(),
    DataExample[Response]>=DataExample[Response_Target] , "Response after 8 AM",BLANK())

     

    Note. I used French Date format for my demo, you can quickly adapt 

    Regards

    tJohn

     

    • kmcardle's avatar
      kmcardle
      Icon for Helper I rankHelper I
      4 years ago

      tJohn, thank you for offereing your expertise.  I have a few things to get finished this morning and will plan to test this a bit later.  I'm so grateful you offered your expertise.

       

      Kevin

  • kmcardle's avatar
    kmcardle
    Icon for Helper I rankHelper I
    4 years ago

    If you have any thoughts that I can try.  I'd be so grateful.  

    And please let me know if more information is needed.  This was my first post here and I wasn't 100% sure on whether people here prefer direct or verbose information.   Thank you all.

    • kmcardle's avatar
      kmcardle
      Icon for Helper I rankHelper I
      4 years ago

      I may have found the issue.  I recreated the same info using a New Measure rather than a New Column and Am now getting a correct answer from the rows I have double-checked.  Here is the Formula I use: 

      My Time to Respond = (((DATEDIFF('SRInsights-SupportCases'[Date/Time Opened], 'SRInsights-SupportCases'[First Response Date], MINUTE))))

      Now I am wondering how I can create a measure from the table that would allow me to Test whether the Open Time is After 6pm and the Resolution Time is After 8am. 

      Any ideas??