cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mahawkins3 Regular Visitor
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
erik_tarnvik Established Member
Established Member

Re: Calculated column with DATEADD

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:

image.png

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.

View solution in original post

2 REPLIES 2
Highlighted
erik_tarnvik Established Member
Established Member

Re: Calculated column with DATEADD

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:

image.png

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.

View solution in original post

mahawkins3 Regular Visitor
Regular Visitor

Re: Calculated column with DATEADD

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 155 members 1,824 guests
Please welcome our newest community members: