days between two integer date fields in Direct query mode

[adegrno]

Hello all,

I have 2 integer date fields (YYYYMMDD) in my fact table with several million records, and I work in direct query mode.

I have trouble doing the nescessary calculations (f.eks. number of days between the two dates) because PBI will not allow me to do the nescessary field transformations or adding of columns.

Importing the table is not an option, it will take too much time. Any suggestions or workarounds for this issue?

Thanks,

[dax]

Hi adegrno,

If you are using SQL server direct query, you coiuld try to achieve this in M code like below

= Table.AddColumn(dbo_test, "Custom", each Duration.Days(Duration.From(Date.FromText(Text.From([ET]))-Date.FromText(Text.From([ST])))))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adegrno]

Thanks for yr reply Zoe, is there something that should be written in front of the "="?

= Table.AddColumn(dbo_test, "Custom", each Duration.Days(Duration.From(Date.FromText(Text.From([ET]))-Date.FromText(Text.From([ST])))))

the "dbo_test" I replace with the table name in which my integer date columns are. When I write the table name it will answer the table name is not correctly spelled. I am 100% sure it is.

the ST and ET I have no problems with.

I am connected to a SQL server 

Regards,

[adegrno]

Hello again

ok so I checked the syntax and it works just fine as long as I am in query editor mode.

When I wish to close and use the results in the model PBI will state that this step contains a query that is not supported in direct query mode.

So, I see the Custom column, the values are correct in the column (meaning your M does the trick) but Power bi will not let me use it.

Any suggestions?

Regards,

Albert

[dax]

Hi adegrno.

I forget this is direct query mode, you could try to achieve this in sql query like below

select name, amount,  convert(datetime,convert(varchar(20),st))   as st, convert(datetime,convert(varchar(20),et))   as et  from test

You could convert this in sql, then use converted data in powerbi.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.