cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwieczerzak Frequent Visitor
Frequent Visitor

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

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Baskar Super Contributor
Super Contributor

Re: Finding difference in hours between two dates.

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

View solution in original post

Moderator v-sihou-msft
Moderator

Re: Finding difference in hours between two dates.

@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,

 

View solution in original post

9 REPLIES 9
tringuyenminh92 New Contributor
New Contributor

Re: Finding difference in hours between two dates.

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

Baskar Super Contributor
Super Contributor

Re: Finding difference in hours between two dates.

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 Frequent Visitor
Frequent Visitor

Re: Finding difference in hours between two dates.

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

jwieczerzak Frequent Visitor
Frequent Visitor

Re: Finding difference in hours between two dates.

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?    

Super User IV
Super User IV

Re: Finding difference in hours between two dates.

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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Baskar Super Contributor
Super Contributor

Re: Finding difference in hours between two dates.

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

View solution in original post

Moderator v-sihou-msft
Moderator

Re: Finding difference in hours between two dates.

@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,

 

View solution in original post

imy Regular Visitor
Regular Visitor

Re: Finding difference in hours between two dates.

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 Super Contributor
Super Contributor

Re: Finding difference in hours between two dates.

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors