Unable to calculate the difference between two dates in a Direct Query model

[rhiheu]

Hi,

I'm having trouble calculating the difference between two date fields in a Direct Query model, I'm looking for the number of hours between them. I've tried three different calculated column formula options and a measure formula and none flag an error in completing the formula, but then when I try to put any of them into a table to view, it just says 'can't display the visual - 'couldn't load the data for this visual':

I've also tried two custom column formulas and both say 'This step results in a query that is not supported in DirectQuery mode.

Unfortunately changing to Import mode is not an option.

These are the different formulas I've tried:

Measure:

• DurationMeasure = SUM('DB'[end_date_time]) - SUM('DB'[start_date_time])

Calculated column:

• DurationDateDiff = DATEDIFF('DB'[end_date_time],'DB'[start_date_time],HOUR)
• DurationDateValue = DATEVALUE('DB'[end_date_time]) - DATEVALUE('DB'[start_date_time])
• DurationSubtraction = 'DB'[end_date_time] - 'DB'[start_date_time]

Transform data > Add custom column:

• = Table.AddColumn(DB, "DurationCustomColumnDuration.Hours", each Duration.Hours([end_date_time]-[start_date_time]))
• = Table.AddColumn(DB, "DurationCustomColumn", each [end_date_time] - [start_date_time])

Any help would be appreciated, thanks.

[DataInsights]

@rhiheu,

What database are you using? I got the measure below to work in a DirectQuery table that uses SQL Server:

Duration Hours = ( MAX ( FactTable[EndDateTime] ) - MAX ( FactTable[StartDateTime] ) ) * 24

[rhiheu]

Hi @DataInsights, sorry I forgot to mention that - it's a PostgreSQL database. Thanks for the suggestion, unfortunately

Duration Hours = ( MAX ( FactTable[EndDateTime] ) - MAX ( FactTable[StartDateTime] ) ) * 24

Also doesn't error when creating it, but then says 'Can't display the visual' when I try and put in a table. 

[DataInsights]

@rhiheu,

Have you tried using custom SQL in the PostgreSQL connector?

[rhiheu]

Hi @DataInsights, no I haven't, how do I use custom SQL in the PostgreSQL connector?

[DataInsights]

@rhiheu,

In Advanced options, enter custom SQL in the "SQL statement" box: