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.
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
- 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
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.
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.
Some more sample of WEEKNUM and WEEKDAY
The TODAY, DATEDIFF, DATEVALUE, DAY Functions
Today() function is self-explanatory. It returns today’s date, but not the time.
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.
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.
The DAY function is very simple. It returns the day number from 1 to 31.
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.
As the name suggested, the HOUR, MINUTE and SECOND functions extract the hour, minute and second information from a given date and time value
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.
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.
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.
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.
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
Please find the code in the below location
DAX — Chapter 11
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.