Forum Discussion

mahawkins3's avatar
mahawkins3
Helper I
8 years ago
Solved

Calculated column with DATEADD

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 ...
Need Help
  • erik_tarnvik's avatar
    erik_tarnvik
    8 years ago

    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.

erik_tarnvik's avatar
erik_tarnvik
Solution Specialist
8 years ago

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's avatar
    mahawkins3
    Helper I
    8 years ago

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