Forum Discussion

ARob198's avatar
ARob198
Icon for Helper IV rankHelper IV
6 years ago
Solved

DATEADD in a calculated Column?

Hello,   I am trying to use IF and DATEADD in a calculated column.  It doesn't seem to be working right.  Does anyone have suggestions?  What I would like the formula to do is to look up the month ...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi ARob198 ,

     

    The DATEADD() function can be used in a calculated column and measure, and require contiguous date selection in it . You may create calendar table first of all, create relationship between the fact data table and calendar table on date field. then use the Calendar'[Date] in DATEADD .

     

    Calendar= CALENDARAUTO()

     

    If you need to pull the values for a previous month in a calculated column, your formula " CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH))"  is ok.

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

ARob198 , I am not able to relate this formula  with your requirement

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

  • ARob198's avatar
    ARob198
    Icon for Helper IV rankHelper IV
    6 years ago

    I am not sure what you would like me to share. Is it possible to use DATEADD in a calculated column?  I guess let's start there.  Is that possible or is DATEADD only for use in measures?

     

    If DATEADD is only for measures, what is the equivalent function for a calculated column?  How can you pull the values for a previous month in a calculated column for use in a calculation?

    • v-xicai's avatar
      v-xicai
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi ARob198 ,

       

      The DATEADD() function can be used in a calculated column and measure, and require contiguous date selection in it . You may create calendar table first of all, create relationship between the fact data table and calendar table on date field. then use the Calendar'[Date] in DATEADD .

       

      Calendar= CALENDARAUTO()

       

      If you need to pull the values for a previous month in a calculated column, your formula " CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH))"  is ok.

       

      Best Regards,

      Amy 

       

      Community Support Team _ Amy

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.