Finding difference in hours between two dates.

[jwieczerzak]

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].[Date], 'prod agenttask'[TaskEndTime].[Date], MINUTE )

I have also tried...

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

but I get the following. 

In DATEDIFF function, the start date cannot be greater than the end date

[Baskar · Accepted solution]

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

[jwieczerzak]

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?    

[v-sihou-msft · Accepted solution]

@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 · Accepted solution]

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

[imy]

Hi Baskar,

I was looking at your solution and I would like to use it for my visualization, but it returns only 00:00.

I want to add a new column in my data set that shows the hours it took to resolve a ticket from the beginning (Date) to end (Updated). Both columns date types are Date/Time, also the new column. There are no nulls or blanks and no dates in the Updated columns are smaller than Date column. What am I doing wrong?

test = if ( Isblank(WHD_Tickets[Date])=true() || Isblank(WHD_Tickets[Updated])=true ||

WHD_Tickets[Date] > WHD_Tickets[Updated],

Blank(),

DATEDIFF (WHD_Tickets[Date], WHD_Tickets[Updated], HOUR )

)

[Baskar]

If possible share some sample data i will have look on it @imy 

[parry2k]

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.

[Baskar]

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]

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

[tringuyenminh92]

Hi  @jwieczerzak,

Please check your data again to ensure there is no start date greater than end date. and could you try add time part to your date column?, there are rows with same dates could be the problem. if it's still not working, please share your sample data so I could proceed further analysis