How to Work with 17+ Date and Time Functions in DAX - DAX in Power BI - Chapter 11

We are going to look at 23 date and time functions in more detail with examples.


As per Microsoft documentation, there are 23 date and time functions are available in DAX. These functions are more or less similar to excel date-time functions. The only difference is that the DAX function can use DateTime data type and can take values from a column as an argument.

In this blog, we are going to explore them with some hands-on examples. 

Get Data

At first import, the US Super Store data from Kaggle and save it to Power BI Desktop.

  • Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
  • Select the file named US Superstore data.csv
  • After selecting the file, data will be displayed in the below format
Image by Author
  • Click on Load and save data.

Use of 8 functions to make a Date table

You are aware that you can create an automatic date table using the DAX function and some additional date-time functions can help you to make a proper date dimension table. 

Date table with CALENDER

DAX Date and Time functions , Power BI
Image by Author

From the above example, you can observe that one date table has been created using CALENDER, DATE, YEAR, QUARTER, MONTH, WEEKNUM and WEEKDAY date-time functions. 

CALENDER() → It returns a table with a single date column. It takes the start date and end date as arguments.

DATE() → It takes arguments as year, month, day and returns specified date with DateTime format.

YEAR() -> It takes a date as an argument and returns a year value which is a four-digit integer.

QUARTER() -> It takes a date as an argument and returns the quarter value which is a number from 1 to 4.

MONTH() -> It takes a date as an argument and returns the month number which is from 1 to 12.

WEEKNUM() → It takes a date as an argument and returns the week number for the specified date and year according to the return_type value. 

WEEKDAY() → It takes a date as an argument and returns a number from 1 to 7 based on the day of the week.

Here is the table visual with the newly created date time.

DAX Date and Time functions , Power BI
Image by Author

Date table with CALENDERAUTO

With CALENDERAUTO function Power BI automatically finds the start date and end date from the data model where some date fields exist. 

But it has one limitation. If your data model contains some dates that are not used for analysis, like date of birth, then it will pick those. This will give a much wider date range than you want.

Date and Time functions of DAX in Power BI
Image by Author

Some more sample of WEEKNUM and WEEKDAY

Date and Time function in DAX , Power BI
Image by Author — WEEKNUM
Date and Time function in DAX , Power BI
Image by Author — WEEKDAY

The TODAY, DATEDIFF, DATEVALUE, DAY Functions

Today() function is self-explanatory. It returns today’s date, but not the time.

Date and Time function in DAX , Power BI
Image by Author

The DATEDIFF function returns the total amount of time between two specified dates. The best part is you can get the results in terms of seconds, minutes, hours, days, weeks, months, quarters, or years. 

Date and Time function in DAX , Power BI
Image by Author

The DATEVALUE function considers a date value that is in the text format and converts to date in date-time format. If you are not mentioning any year, by default it will consider the current year.

Date and Time function in DAX , Power BI
Image by Author

The DAY function is very simple. It returns the day number from 1 to 31. 

Date and Time function in DAX , Power BI
Image by Author

The NOW, HOUR, MINUTE, SECOND, TIME, TIMEVALUE functions

The NOW function returns the current date as well as the current time. This is the main difference with the TODAY function. 

Date and Time function in DAX , Power BI
Image by Author

As the name suggested, the HOUR, MINUTE and SECOND functions extract the hour, minute and second information from a given date and time value

Date and Time function in DAX , Power BI
Image by author

The TIME function returns a time in DateTime format if you pass hours, minutes and seconds as numbers.

Whereas the TIMEVALUE returns a time in DateTime format if you pass time in text format.

Date and Time function in DAX , Power BI
Image by Author

The EDATE, EOMONTH Functions

The EDATE function considers two arguments, the first one is the date and the second is the number of months. It returns the date with the specified number of months before or after the given start date.

Date and Time function in DAX , Power BI
Image by Author

Whereas the EOMONTH function is similar to the EDATE function, except it returns the last date of the month where the specified number of months is before or after the given start date.

Date and Time function in DAX , Power BI
Image by Author

The UTCNOW and UTCTODAY Functions

Using the UTCNOW and UTCTODAY functions, you can get the current timestamp and current date for the UTC (Coordinated Universal Time) time zone.

Date and Time function in DAX , Power BI

The YEARFRAC Function

The YEARFRAC function calculates the fraction of the year represented by the number of whole days between two dates.

If you want to know in detail, please refer to Microsoft documentation.

The syntax of the YEARFRAC function is as follows:

YEARFRAC ( <StartDate>, <EndDate> [, <Basis>] )

Values for Basis →

0 — US (NASD) 30/360
1 — Actual/actual
2 — Actual/360
3 — Actual/365
4 — European 30/360

Date and Time function in DAX , Power BI
Image by Author

Download

Please find the code in the below location

DAX — Chapter 11 

https://github.com/arpitag1/Power-BI

Video

Conclusion

In this blog, we learn the 23 date and time functions of DAX with practical examples.

If you have any questions related to this project, please feel free to post your comments.

Please visit my website for other technical resources.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: