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.