Forum Discussion

jwieczerzak's avatar
jwieczerzak
Regular Visitor
9 years ago
Solved

Finding difference in hours between two dates.

I am using the folloiwng to create a column but the output seems to be incorrect (all zeros showing).  Any help would be appreciated...   Duration3 = DATEDIFF ( 'prod agenttask'[TaskStartTime].[Dat...
  • Baskar's avatar
    Baskar
    9 years ago

    Hi Dude ,

     

    Did u tried this one , 

     

    Duration3 = if ( Isblank('prod agenttask'[TaskStartTime])=true() ||  Isblank('prod agenttask'[TaskEndTime])=true ||

                              'prod agenttask'[TaskStartTime] > 'prod agenttask'[TaskEndTime],

                               Blank(),

    DATEDIFF ( 'prod agenttask'[TaskStartTime], 'prod agenttask'[TaskEndTime], MINUTE )

    )

     

     

    Am sure it will help u .

     

    Note :

           Check Both Date column Data Type.

     

     

    If not solve your prob share your data i will help uuuuuu

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    jwieczerzak

     

    This is the limitation of the DATEDIFF() function in DAX. An error is returned if start_date is larger than end_date. For more details, please see: DATEDIFF Function (DAX). So in your scenario, you have to apply condition (like IF statement) to make the end date larger than start date.

     

    Regards,

     

jwieczerzak's avatar
jwieczerzak
Regular Visitor
9 years ago

Yes.  It turns out that there are end dates that preceed start dates.  But why does Power BI fail on that?  Wouldn't if just produce a negative number?    

parry2k's avatar
parry2k
Super User
9 years ago

unfortunatley it doesn't return negative number if end date is earlier than start date, but you can add login in your if condition

 

if end date < start date then -(datediff(enddate, startdate, minute)) else datediff(start date, enddate, minute)

 

This will give you negative number when end date is earlier than start date and otherwise you will get positive number. Hope it is helpful.