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

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

The HOUR function

The HOUR function is one of the Date and Time functions.  It returns a number between zero [0] and 23, where zero [0] represents 12:00 AM and 23 represents 11:00 PM.  It employs the following syntax:

This function has one [1] argument:

• datetime: this is required and can be a datetime value obtained from a column in your data model, a calculation involving date and time functions, or even a text string representing a time.

The function returns a numerical value:

• the hour component of the provided datetime value as a number between zero [0] and 23.

You should note the following:

• you should input the time through a date / time function, expression or directly, in acceptable formats.  Text representations of time are also valid inputs
• for text representations of date and time, the function interprets the time based upon the computer's locale, using colons as standard time separators.

Let’s consider the following example where we have the tbl_Sales Table:

Let’s write the following DAX to extract the hour from the Order Time field:        

In the EVALUATE statement, we construct the following:

• the ADDCOLUMNS function is used to add a new column to the Table named Hour
• the first argument in this function is the table  tbl_Sales to which you want to add a new column
• the second argument is a list of expressions that define the new columns to be added.  Each expression has two [2] parts:
  •  the name of the new column (e.g. Hour)
  •  the HOUR function to extract the hour values from the Order Time column. 

In the EVALUATE statement, we assigned the extracted hour values to the new Hour column:

Important notes:

• the HOUR function is case-insensitive for text input
• as stated above, when using text input, the function relies upon your system's locale settings to interpret the format.  Make sure your time format aligns with your locale to avoid unexpected results
• for more advanced time manipulations, you can explore other DAX functions like MINUTE, SECOND, TIME, and DATE.

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.