Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mahawkins3
Helper I
Helper I

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
erik_tarnvik
Solution Specialist
Solution Specialist

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
erik_tarnvik
Solution Specialist
Solution Specialist

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.