Forum Discussion

Frequent Visitor

10 years ago

Solved

Issue with DATEADD function in Direct Query Mode

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 calcula...

Eric_Zhang
10 years ago
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.

Frequent Visitor

10 years ago

Hi Ankit,

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

Icon for Microsoft Employee rank

Microsoft Employee

10 years ago

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.

asriniva33
Frequent Visitor
10 years ago
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
  Microsoft Employee
  10 years ago
  asriniva33
  
  Try a calculated column as below.
  
  Month= SWITCH(MONTH('Sales Currency'[ModifiedDate]),1,"Jan",2,"Feb",6,"Jun")
  - CraigBlackman
    Helper III
    9 years ago
    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?
fguiroy
Frequent Visitor
4 years ago
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

Related content