Power Pivot Principles: The A to Z of DAX Functions – DATEVALUE

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DATEVALUE.

The DATEVALUE function

The DATEVALUE function is one of the date and time functions.  It is used to convert a date in the form of text to a date in datetime format.  It operates using the following syntax:

There is only one [1] argument for this function:

• date_text: this argument is required, and is a text string that represents a date.

It should be noted that:

• the DATEVALUE function uses the locale settings of the model to understand the text value specified within date_text when performing its conversion
• if the locale settings of the model represent dates in the format of Month/Day/Year, then the string “1/8/2020” will be converted to a datetime value of January 8^th, 2020
• if the locale settings of the model represent dates in the format of Day/Month/Year, then the string “1/8/2020” will be converted to datetime value of August 1^st, 2020.
• if the year portion of date_text is omitted, the DATEVALUE function uses the current year from your computer’s built-in clock and time information in date_text is ignored.

Consider the following example DAX query:

Under our locale settings (DD/MM/YYYY) this code will return as follows:

However, the last two dates may also return the 10^th August and the 2^nd June respectively.  This is dependent upon your machine’s date and time settings.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.