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

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

The DAY function

The DAY function is one of the date and time functions.  It returns a number from one [1] to 31 representing the day of the month.  Its syntax is as follows:

It has one [1] argument:

• date: this argument is required, and it is a date in date time format or a text representation of a date.

If the argument is a string, it is translated into a datetime value using the same rules applied by the DATEVALUE function.

Consider the following example, where we will obtain the day from a date column.  We have the following data (not displayed in full): 

We can write the following DAX query to add a column to this data containing the day of the month from the column OrderDate:

This DAX query will append a column named “Sales Day” to the end of the table containing the date of the month:     

But wait, there’s more.  Consider the following example where we extract the day from a date represented as text.  We can write the following DAX query:

The DAX query above stores the string date of “16/03/2020” into the variable StringDate.  We can then use the DAY function to extract the day from StringDate.  This will return the following value:

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.