Forum Discussion
Days between two dates columns in DirectQuery
Hello community,
I tried, unsuccesfuly, to subtract two date in DirectQuery but impossible..
How can I do this?
12 Replies
I have been able to get this working by using the DATEVALUE DAX command. This seems to reconfirm that these are Date fields.
Please check the results you get as the first time I tried this the results worked on 90% of the calculations. Had to refresh to ensure all values were correct.
Example :
DATEVALUE(Table_Name[Date_Field_One]) - DATEVALUE(Table_Name[Date_Field_Two])
The returning field can then be formatted as required.
Mi2nMicrosoft Employee
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?
pqianricgin, 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
Hi, so I found a way to make it work: First I change the type of the respective date column (e.g. [birthday]) from Date (or Date Time) to Whole number. Then create a new column with the formula DateTimeLocalNow(); I called it "NOW". After this change this column-type to a whole number as well (it needs to be an extra step). Then add a new colum eg "AGE" with a simple subtraction: "[NOW] - [birthday]". It will work with direct query and it will work, when opening visuals in powerbi online. Hope it helps! Please mark it as solution, if it works for you as well, THX!
