Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wrivers626
Frequent Visitor

DATEDIFF and True Number of Hours Returned

Hello everyone,

 

I was hoping someone could help me.  I have searched the posts with regard to using DATEDIFF but I haven't found anything that can help me solve this issue.

 

I have two columns that are in datetime format: 1/01/2018 00:00:00 AM and I have created a calculated column Approval to SchedStart that shows the hours difference between the two datetimes.

 

Column A is the start date and column B is the end date and I am using HOUR as the interval so it looks like this:
Approval to SchedStart = DATEDIFF('Table'[ColumnA].[Day], 'Table'[ColumnB].[Date],HOUR)

 

The problem I am running into the value of Approval to SchedStart is being rounded instead of showing what the actual number of hours between the two datetime fields are.  So for instance, I only get 0,24,48, 72 etc., instead of the actual hours like 5,9,22.5 etc.  Does anyone know how to get that actual number of hours?  Thanks!

1 ACCEPTED SOLUTION

You could try this quick and dirty trick to resolve that error

 

Approval to SchedStart  = IFERROR( DATEDIFF('Table'[ColumnA], 'Table'[ColumnB], HOUR ),-999)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

HI @wrivers626

 

Please remove the .[Date] and .[Day] off your column references.

 

Please try this

 

Approval to SchedStart = DATEDIFF('Table'[ColumnA], 'Table'[ColumnB], HOUR )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark!  Sorry for the late reply.  I was traveling and hadn't had a chance to check emails.  I did try to remove the .[Date] and .[Day].  However, when I did that the error "In DATEDIFF function, the start date cannot be greater than the end date"  I know that this is not the case. 

 

The "Start Date" which is column A is always an "Approval Date" and the "End Date" which is column B is always a "Scheduled to Start" date.  So first there has to be an approval (Column A) which in turn creates a scheduled to start date (Column B).  See below.  Thanks!

Change Approval Date           Change Scheduled Start Date                Difference In Hours

10/12/2017 2:31:41 PM              10/13/2017 10:00 AM                     Should give total hours diff

 

You could try this quick and dirty trick to resolve that error

 

Approval to SchedStart  = IFERROR( DATEDIFF('Table'[ColumnA], 'Table'[ColumnB], HOUR ),-999)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark that worked!  Thanks so much!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.