Issue with DATEADD function in Direct Query Mode

[asriniva33]

Hi ,

I am working in Direct Query Mode . And i wanted to shift my date column say by "2 days".  When using DATEADD function, I am getting an error "DATEADD function is not allowed as part of calculated column DAX expressions on DirectQuery models". Could anyone please let me know if there is a work around to establish this without using DATEADD function?

Regards,

Srinivas

[ankitpatira]

@asriniva33

In power bi desktop go to file, options and settings, options, direct query and make sure checkbox "Allow unrestricted measures under direct query" is ticked.

[asriniva33]

Hi Ankit,

I have tried that option too, but still its throwing the same error.

[Eric_Zhang · Accepted solution]

@asriniva33

I can't find DATEADD in DirectQuery either, even with that option enabled. As a workaround, to shift date by days, try [date]+1/[date]-1. Or with DATE(YEAR([dateCol]),MONTH([dateCol]),Day([dateCol])+1) by Year, Month, Day.

Thanks for your feedback, we will report and confirm this internally and would post back if there comes any update.

[fguiroy]

Hi @asriniva33 

 I think i have a similar challenge as yours. I needed to find a Today()+5 To create an emergency tag/conditional formatting for a Due Date field. 

 I was able to solve it following this steps -> 

1) I defined a Dax Measure for the Date +5 > 

Days for Due DATE = TODAY()+5

2) Once i was able to have that, i created a calculated column like this ->

Days after expiration = DATEDIFF([Days for Due DATE],TABLE[DueDate],DAY)

3) Having that difference i was able to create the formatting for the DueDate Column with the different colours

Hope this solution helps!

Francisco

[asriniva33]

@Eric_Zhang

Thanks for the workaround.

I am trying to create a calculated column for the "Month Names(Ex. January etc.)" from the exisiting Date Column using FORMAT function. But when i using FORMAT function,i am getting an error "FORMAT function not supported in Direct Query Mode". Could you please let me know if there is any way to create a calculated column for month names in Direct Query Mode.

[Eric_Zhang]

@asriniva33

Try a calculated column as below.

Month= SWITCH(MONTH('Sales Currency'[ModifiedDate]),1,"Jan",2,"Feb",6,"Jun")

[CraigBlackman]

I am having a similar problem in that i want to move transactions that occure between 00:00:00 and 07:00:00 to the previous day and cannot using DIRECT QUERY. Did anyone find a work around?

[gustavoleo]

I vae an issue also! On my case the function dont work