DATEADD in a calculated Column?

[ARob198]

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)))))))

[amitchandak]

@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]

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 · Accepted solution]

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.