Issue related to DATE function.

[Saurabh8437]

Hi All,

I am experiencing this issue from last few days. Earlier it was fine. I am not sure whether I have touched upon any settings or it is a new functionality by Microsoft. 

I am facing the same issue in both Power BI and Excel.

Problem: Whenever I am using Date function and any irrelevant date comes, it is giving proper date instead of throwing error.

Ex: =DATE(2020,1,35), it should ideally throw error, but now it is giving me proper date as result which is 4th Feb 2020.

The same happening with months as well. Ex. 

=DATE(2020,13,1) giving answer as 1/1/2021.

Any advise?

It is creating problem in data validation as there are wrong date entries in the data and now can't find them due to this issue. Have to do multiple checks to find out those wrong dates.

Any help will be appreciated.

[v-xianjtan-msft · Accepted solution]

Hi @Saurabh8437 

In Excel and Power BI, the DATE function behaves in a way that automatically adjusts the date even if the date or month entered is outside the normal range. This behavior is by design and is not an issue.

Please refer to these links:

https://learn.microsoft.com/en-us/dax/date-function-dax#parameters 

DATE function - Microsoft Support

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xianjtan-msft · Accepted solution]

Hi @Saurabh8437 

In Excel and Power BI, the DATE function behaves in a way that automatically adjusts the date even if the date or month entered is outside the normal range. This behavior is by design and is not an issue.

Please refer to these links:

https://learn.microsoft.com/en-us/dax/date-function-dax#parameters 

DATE function - Microsoft Support

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xianjtan-msft · Accepted solution]

Hi @Saurabh8437 

Since using the DATE function in Power BI automatically adjusts the date, it is recommended that you consider some of the following alternatives to validate the date.

1. In the Power Query editor, click Add Custom Column. Enter a custom column name (eg: “ValidDate”) and use the following M code to validate the date:

try 
    if [Year] >= 1900 and [Year] <= 2100 and 
       [Month] >= 1 and [Month] <= 12 and 
       [Day] >= 1 and [Day] <= Date.DaysInMonth(#date([Year], [Month], 1)) 
    then #date([Year], [Month], [Day]) 
    else null
otherwise null

2. Create a calculated column using the following DAX:

Valid Date = 
SWITCH(
    TRUE(),
    'Table'[Year] < 1900 || 'Table'[Year] > 2100, BLANK(),
    'Table'[Month] < 1 || 'Table'[Month] > 12, BLANK(),
    'Table'[Day] < 1 || 'Table'[Day] > DAY(EOMONTH(DATE('Table'[Year], 'Table'[Month], 1), 0)), BLANK(),
    DATE('Table'[Year], 'Table'[Month], 'Table'[Day])
)

I hope these alternatives are helpful! 

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Saurabh8437 I can confirm this but I'm not sure if the functionality changed or when it changed.

[Saurabh8437]

Thanks for confirming, is there any way to fix it?