Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

0
Nathan

Bug: Date Function Issue

Submitted by on ‎08-11-2017 02:10 PM

There's an issue with the DATE function supporting 0 day, which should represent the last day of the previous month.

 

Measure = DATE(2017, 1, 0)

The expected outcome on this should be December 31, 2016, but instead an error displays stating "An argument of function 'DATE' has the wrong data type or the result is too large or too small".

Status: Delivered
See more ideas labeled with:
7 Comments (7 New)
Comments
v-haibl-msft
Microsoft Employee
Microsoft Employee
‎08-14-2017 02:39 AM

@Nathan

 

I can repro the same issue as you. I’ve reported it internally to Power BI Team: CRI 44660354
I’ll post here once I get any update about it.

 

Now you can use  Date ( 2017, 1, 1) - 1 as a workaround.

 

Best Regards,
Herbert

Vicky_Song
Impactful Individual
Impactful Individual
‎08-14-2017 02:39 AM
Status changed to: Accepted
 
v-haibl-msft
Microsoft Employee
Microsoft Employee
‎08-14-2017 06:52 PM

@Nathan

 

I've got response from the Product Team.

 

DAX DATE function is different from Excel's same named function in that the former only supports valid dates while Excel's version does date shifting if the input parameters correspond to invalid dates. This is by design.

 

Best Regards,
Herbert

Vicky_Song
Impactful Individual
Impactful Individual
‎08-14-2017 06:54 PM
Status changed to: Delivered
 
Nathan
Frequent Visitor
‎08-14-2017 07:42 PM

@v-haibl-msft,

 

Thanks for getting an answer and a workaround for this defintely helped out. However, I'm not sure I'm completely following on the provided resolution. It sounds like the DATE function in DAX should only work with valid dates, but the DATE DAX function follows the same rules for date shifting as the Excel version with the exception of the 0. Along with the Microsoft documentation using the date shifting features in the examples for the DATE DAX function.

 

 

So for instance, the below DAX calculation would produce a valid date of Decemeber 30, 2016.

 

 

Measure = DATE(2017, 1, -1)

 

Does this mean the above calculation would not be supported? Or just the case when the day parameter is zero?

 

Thanks for all the help,

 

Nathan

 

v-haibl-msft
Microsoft Employee
Microsoft Employee
‎08-15-2017 07:57 PM

@Nathan

 

It should be the case when day parameter is zero. According to Microsoft documentation, negative integer is supported in day parameter.

 

Date Function Issue_1.jpg

 

Best Regards,
Herbert

Nathan
Frequent Visitor
‎08-15-2017 09:06 PM

@v-haibl-msft,

 

You are correct; It does not explictly say 0 is supported. The point I'm trying to expose is DAX Date supports negative numbers, but fails to allow you to go back a single day (which appears to be where 0 comes into play).

 

DATE(2017, 1, -2) = December 29, 2016
DATE(2017, 1, -1) = December 30, 2016
DATE(2017, 1, 1) = January 1, 2017

Notice how I cannot reach December 31, 2016 using date shifting without using the 0 parameter. That is the bug I'm attempting to report. Without the ability to go back a single day using date shifting, the entire concept feels broken.

 

Appreciate you taking the time to explain everything to me. I'll continue to use the workaround you provided as it appears to correctly handle date shifting.

 

Thanks,

 

Nathan

Idea Statuses