Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ricgin
Regular Visitor

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?

 

 

 

10 REPLIES 10
NRB_UK
New Member

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.

 

 

Mi2n
Employee
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.

ricgin
Regular Visitor

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

 

 

Anonymous
Not applicable

I get the same result via this method. 

  ! This step results in a query that is not supported in DirectQuery mode.

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

Anonymous
Not applicable

Me too! 

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]

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.