Forum Discussion
Issue related to DATE function.
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.
Hi Anonymous
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.Hi Anonymous
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 null2. 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.
4 Replies
Hi Anonymous
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.Hi Anonymous
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 null2. 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.
Anonymous I can confirm this but I'm not sure if the functionality changed or when it changed.
