Possible error on Date Dax Function Definition

[djbc1986]

Hi there

In theory, in this definition, I should be getting an error with a negative value for the year. However, what I'm seeing is the minimum year (1900) minus the negative parameter.

For values greater than 9999 or less than zero (negative values), the function returns a #VALUE! error.

Am Idoing something wrong or misunderstanding something?

Regards

[az38 · Accepted solution]

Hi @djbc1986 

I think it works correct

so, zero date is 1900, minus 1 is 1899 and it is ok, it could be useful forsome historical data

Plus 1 year to 9999 has the other widely-known 10000 year problem, you could read for example here https://en.wikipedia.org/wiki/Year_10,000_problem 

also, SQL datetime range is up to 9999 https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15 

do not hesitate to give a kudo to useful posts and mark solutions as solution

[djbc1986]

Hi @az38 

It works, no problem with that. The definition of the function is the one that is not correct:

This is what I mean.

Regards.

[az38]

@djbc1986 

unfortunately, MSDN is not the best and not the completely correct and full DAX documentation source.

even microsoft people refers to https://dax.guide/date/  , but there is no mention about function behaviour outside the 0-9999 range, only here https://dax.guide/dt/datetime/ 

The first date officially supported by DAX is March 1, 1900. Date calculations executed on time periods prior to that date might lead to errors and should be considered as inaccurate.

do not hesitate to give a kudo to useful posts and mark solutions as solution