Forum Discussion

asriniva33's avatar
asriniva33
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...
Need Help
  • Eric_Zhang's avatar
    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.

ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity Champion
10 years ago
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's avatar
asriniva33
Frequent Visitor
10 years ago

Hi Ankit,

 

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

 

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    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.

    • asriniva33's avatar
      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's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        asriniva33

        Try a calculated column as below.

        Month= SWITCH(MONTH('Sales Currency'[ModifiedDate]),1,"Jan",2,"Feb",6,"Jun")
    • fguiroy's avatar
      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