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 ...
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

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