Forum Discussion
Days between two dates columns in DirectQuery
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..
ricgin Given that the operation folds to SQL, it should work just fine in DQ mode.
What errors are you getting if you try this? What's the formula in the formula after the substraction?
when I create a new column (in the query editor) to substract 2 columns, PowerBI Desktop said that : if I want to apply the changes I need to switch to import mode
I get the same result via this method.
! This step results in a query that is not supported in DirectQuery mode.