Forum Discussion

ARob198's avatar
ARob198
Helper 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.

ARob198's avatar
ARob198
Helper 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
Community 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.