Difference between two date columns in Direct Query mode

[sfshariff]

Hi,

I am trying to create a calculated column showing the difference between two date columns (duration, i.e number of days). However, DATEDIFF function is not supported in Direct Query mode. I also tried doing simple subtraction between the dates: datedifference = 1.*([createddate]-[closeddate]) and I get the 'ORA-00904: "c168": invalid identifier. The exception was raised by the IDbCommand interface.' error

Is there a workaround to get the difference between two date columns in Direct Query mode?

Thanks,

Sana

[v-chuncz-msft]

@sfshariff,

I cannot reproduce this issue. Please share us more detailed information about the data source.

[Salvador]

Hi,

Try creating a column with this (You'll need to replace the column names to the ones you'll use): 

Collections Delay =

    SWITCH (

    TRUE ();

    Invoices[Collection Date] < Invoices[Overdue Date]; DATEDIFF ( Invoices[Collection Date];Invoices[Overdue Date]; DAY )* -1;

    Invoices[Collection Date] > Invoices[Overdue Date]; DATEDIFF ( Invoices[Overdue Date]; Invoices[Collection Date]; DAY );

    0)