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,

     

Baskar's avatar
Baskar
Resident Rockstar
9 years ago

Cool , 

 

the prob is first part of datediff is should be smaller then next date.

 

My assumptio your prob is u have some null value or some where start date is greater then end date.

 

 

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 )

)

 

 

Try this one it will help u i think so, 

If not let me know then i will help u sure :-)

Cool

  • jwieczerzak's avatar
    jwieczerzak
    Regular Visitor
    9 years ago

    Thanks for the help.  There were nulls so I removed them.  However, I was unable to run your Query because I received an error about multiple isblank statements being invalid.  As a newbie to Power BI I am struggling with the oddities in the syntax...