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 in my date column and do calculation if the month is March, June, Sept, or December.  Perhaps there is an easier way to do this.  So I am open to suggestions or other ideas.

 

If the month is one of those months, then the calculation needs to be as follows:

 

([Fund Val Ex Cash $]) 
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -3, MONTH)))
)

 

I am trying to use the formula below, but it is just pulling the first month's value and dividing by 6.  Does anyone have any ideas?  This needs to be in a calculated column.

 

Thank you!

 

Fund Ex Cash Avg TEST =
if(MONTH('FundCustValAll'[DATE]) = 12,
DIVIDE(
(
([Fund Val Ex Cash $])
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -3, MONTH)))
), 6),
(if(MONTH('FundCustValAll'[DATE]) = 9,
DIVIDE(
(
([Fund Val Ex Cash $])
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -3, MONTH)))
), 6),
(if(MONTH('FundCustValAll'[DATE]) = 6,
DIVIDE(
(
([Fund Val Ex Cash $])
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -3, MONTH)))
), 6),
(if(MONTH('FundCustValAll'[DATE]) = 3,
DIVIDE(
(
([Fund Val Ex Cash $])
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -1, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -2, MONTH)))
+
(CALCULATE([Fund Val Ex Cash $], DATEADD('Calendar'[Date], -3, MONTH)))
), 6),
0)))))))

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.

3 Replies

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