Forum Discussion
Days between two dates columns in DirectQuery
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
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
Me too!