Bug: Date Function Issue

[Nathan]

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

[v-haibl-msft]

@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

[v-haibl-msft]

@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

[Nathan]

@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]

@Nathan

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

Best Regards,
Herbert

[Nathan]

@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