Date Management with DirectQuery connection

[Anonymous]

Hi all,

I am applying a DirectQuery connection to a big table which includes a date field (ACTUAL_DATE). Of course, since this is a DirectQuery connection, I cannot transform this field into a date type, which I find a big limitation in the analysis.

The temporary solution that I've applied is adding an Excel file to the model (Import mode). This file has only one column (NEW_DATE) with all the dates in the same format as the original table, thus I'm able to (1) link it to the orignal table and (2) use this new field as a date type field with all its functionalities. This seems to work just fine.

My question is, is there any alternative to this workaround, avoiding the need to load an additional manual Excel File?

Thank you in advance for your replies.

[v-frfei-msft]

Hi @Anonymous ,

We can create a calculated table to work around like that.

Table = CALENDAR(MIN('Product'[SalesDate]),MAX('Product'[SalesDate]))

[Anonymous]

Hi again,

Any ideas? I'm still lost with this one.

Thanks!

[Anonymous]

Thank you for your reply. Unfortunately this approach is not working, since Power BI is not able to properly calculate the minimum amb maximum values for the field. For further reference, the following KPIs throw the following results:

MinDate = min(DFU_TEST_2[STRT_DT]) => Result when printed as a card: 01/04/2019

MaxDate = max(DFU_TEST_2[STRT_DT]) => Result when printed as a card: 31/12/2018

Therefore when I try your approach I get an error indicating that the minimum date cannot be greater than the maximum one.

Any clues on how to fix this?

[Greg_Deckler]

I would think that the ACTUAL_DATE field should get recognized as a date field in Power BI

[Anonymous]

It can't, as you can see in the following screenshot:

(For some reason I cannot add the image, you can find it here: i.ibb.co/C1JWR6h/error.png

Sorry it's in Spanish. Essentially it is saying that "this step leads to a query that is not compatible with DirectQuery mode" and suggests me to switch to Import, which I don't want.