Forum Discussion
Days between two dates columns in DirectQuery
You might have to enable "Allow unrestricted measures in DirectQuery mode" from File -> Options and Settings -> Options -> DirectQuery, in order to use the DATEDIFF() function in order to subtract two dates.
But, beware, this will not restrict any potentially costly queries if you try to use them.
Thanks, but I have an error message.. DATEDIFF() is not supported in DirectQuery mode..
Maybe avaible in the next release?
ricgin, in the query editor, you should be able to substract two date columns by simply using the "-" operator. There's a ribbon operation for this: select the two columns, go to Add Column -> Time -> Substract.
The resultant column is of type Duration. When loaded into AS this will get converted to a bigint, which is accurate to 0.1ms. Then you can use simple math to extract the portion of this field. For example, to get to the total number of days:
Column = [TimeDifference] / 1E7 / 60 / 60 / 24
It's actually quite interesting to note the way PowerQuery does this substraction...
select ...,
convert(bigint, ( select convert(bigint, convert(bigint, datediff("ns", dateadd("ms", datediff("ms", dateadd("d", datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[StartDate]), convert(datetime2, '0001-01-01 00:00:00')), [_].[StartDate]), dateadd("d", datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[StartDate]), convert(datetime2, '0001-01-01 00:00:00'))), [_].[StartDate])) / 100) + (convert(bigint, datediff("ms", dateadd("d", datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[StartDate]), convert(datetime2, '0001-01-01 00:00:00')), [_].[StartDate])) * 10000 + convert(bigint, datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[StartDate])) * 864000000000) as [$Item] ) - ( select convert(bigint, convert(bigint, datediff("ns", dateadd("ms", datediff("ms", dateadd("d", datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[EndDate]), convert(datetime2, '0001-01-01 00:00:00')), [_].[EndDate]), dateadd("d", datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[EndDate]), convert(datetime2, '0001-01-01 00:00:00'))), [_].[EndDate])) / 100) + (convert(bigint, datediff("ms", dateadd("d", datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[EndDate]), convert(datetime2, '0001-01-01 00:00:00')), [_].[EndDate])) * 10000 + convert(bigint, datediff("d", convert(datetime2, '0001-01-01 00:00:00'), [_].[EndDate])) * 864000000000) as [$Item] )) as [TimeDifference]Thanks, but doesn't work in DirectQuery mode..
I get the same result via this method.
! This step results in a query that is not supported in DirectQuery mode.
