Calculated column with DATEADD

[mahawkins3]

Hi,

I'm getting unexpected results using the DATEADD function in a calculated column.

What I'm trying to do is calculate the date of the start of the week (i.e. "rounding down" to Monday) as a separate column based on an existing date field [SignupDate]. I'm doing this using the following formula:

WeekBeginning = DATEADD(Customers[SignupDate],1-WEEKDAY(Customers[SignupDate],2),DAY)

I would expect this to add 1 minus the WEEKDAY output to the date (e.g. for a Monday the number of days add would be 1-1 = 0; for a Thursday it would be 1-4 = -3). For many records, it seems to do this just fine, but then there are lots of others which are returning blanks, seemingly at random. The [SignupDate] column values are all in date format and are all valid dates, so I don't see what would be causing the issue.

Any idea what might be going wrong?

Thanks in advance.

[erik_tarnvik · Accepted solution]

Hi @mahawkins3,

the dates you expect to generate using WeekBeginning must actually exist in the column you refer to in the first argument to DATEADD. And they must be contigous. Do you have a calendar table with proper relations in your data model?

I'll give you an example. I generate a calendar table using CALENDAR(DATE(2016,1,1),DATE(2016,1,10)). I then added your WeekBeginning as a calculated column. Here is what I get as a result:

You would expect the first three rows of column [WeekBeginning] to contain 12/28/2015 but it doesn't since the [Date] column does not contain this date.

If you have a calendar table and define the proper relation with Customers[SignupDate], your should then use

WeekBeginning = DATEADD('Calendar'[Date],1-WEEKDAY(Customers[SignupDate],2),DAY)

for your calculated column.

[mahawkins3]

Ah, I see what you mean. I've created a related Calendar table as you suggested and now all is fine. Thanks!