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
abaconsup
Advocate I
Advocate I

DateAdd returns blank values

I've been struggling with the DATEADD function today, I was expecting that this function could alter a date such that you can simply add or subtract days, months or years.

 

However, the function appears to only return dates that were in the original data column. i.e. I manually entered a dataset with 3 dates and then created a calculated column that adds a single day to the value. This returns the following values:

PowerBIDateAdd.jpg

 

According to the function description:

https://msdn.microsoft.com/en-us/library/ee634905.aspx

This function "Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context."

 

I presume that what I'm witnessing is this function only being capable of returning dates in the "current context", i.e. those dates within the original column?

 

This poses me a problem, if the dates I want to generate are outside the current context, what is the best way of creating a calculated column that achieves this?

 

For now I've used EDATE to create the required values as I only need to subtract a single year (12 months) from the original date.

 

My version of PowerBI Desktop is 2.37.4464.461 64-bit (July 2016)

1 ACCEPTED SOLUTION
KGrice
Memorable Member
Memorable Member

You are correct about DATEADD. From the article you linked: "The result table includes only dates that exist in the dates column."

 

I think DATEADD is typically used for time intelligence formulas within a measure, when you're only looking to shift your context to a different scope of dates already in your model.

 

If you wanted to add a single day to your Date column using a new column, you could always take the Date column and add 1:

 

Column = TestDates[Date] + 1

 

If you want to subtract a single year, I tried this at first:

 

MinusOneYear = DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date]))

 

That gives me an error ("An argument of function 'DATE' has the wrong data type or the result is too large or too small."), presumably for leap years, though I can't get rid of the error even accounting for leap years with an IF function. If I wrap all of it in IFERROR, it works, and 3/1/2015 is repeated because of the leap year, once for 3/1/2016 and once for 2/29/2016. This prevents the error, but I also don't see any blanks, so I'm not sure what caused the error:

 

MinusOneYear = IFERROR(DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date])), BLANK())

 

You might also try this in M in the Query Editor as a new column there:

 

Custom = Date.AddMonths([Date], -12)

 

That results in 2/28/2015 being repeated instead of 3/1/2015, so you'll have to be careful either way.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

An other work around is to add an auxiliar column year_month_id and work with integers. Ones you have it, you can do a lookup value to +- x*MONTHS to the same table.

 

The problem in my case, was that I had not all dates in the sales table.

 

Example for a 5 month shift:

shifted_date = LOOKUPVALUE(sales[date], sales[year_month_id],sales[year_month_id]-5).
 
 
KGrice
Memorable Member
Memorable Member

You are correct about DATEADD. From the article you linked: "The result table includes only dates that exist in the dates column."

 

I think DATEADD is typically used for time intelligence formulas within a measure, when you're only looking to shift your context to a different scope of dates already in your model.

 

If you wanted to add a single day to your Date column using a new column, you could always take the Date column and add 1:

 

Column = TestDates[Date] + 1

 

If you want to subtract a single year, I tried this at first:

 

MinusOneYear = DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date]))

 

That gives me an error ("An argument of function 'DATE' has the wrong data type or the result is too large or too small."), presumably for leap years, though I can't get rid of the error even accounting for leap years with an IF function. If I wrap all of it in IFERROR, it works, and 3/1/2015 is repeated because of the leap year, once for 3/1/2016 and once for 2/29/2016. This prevents the error, but I also don't see any blanks, so I'm not sure what caused the error:

 

MinusOneYear = IFERROR(DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date])), BLANK())

 

You might also try this in M in the Query Editor as a new column there:

 

Custom = Date.AddMonths([Date], -12)

 

That results in 2/28/2015 being repeated instead of 3/1/2015, so you'll have to be careful either way.

Fantastic feedback, was hoping there would be a succinct alternative.

 

I was hoping to do any data transformation early-on, therefore I like the M option with the AddMonths function, I'll probably use the AddYears function.

 

The leap years/days might require some careful thought, but it may not impact the report we're trying to build.

 

This is great, thanks for your help.

Thanks for the reply with additional info! I looked for AddYears in the list of formulas and must have overlooked it, but it's more straightforward than months in this case.

 

It handles the leap year the same as AddMonths, and I think if I have to duplicate a date on either side, it's better to keep it in February. It makes more sense for month-over-month comparisons.

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.