Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
You could try this quick and dirty trick to resolve that error
Approval to SchedStart = IFERROR( DATEDIFF('Table'[ColumnA], 'Table'[ColumnB], HOUR ),-999)
HI @wrivers626
Please remove the .[Date] and .[Day] off your column references.
Please try this
Approval to SchedStart = DATEDIFF('Table'[ColumnA], 'Table'[ColumnB], HOUR )
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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |