Date and Time Functions in Power BI

This article will help you explore the Date and Time Functions of DAX with some hands-on examples.

Date and Time Functions in Power BI

According to Microsoft’s documentation, DAX has 23 date and time functions. These functions allow you to perform computations using dates and times. Year, Month, Day, Date, Time, Now, Hour, Minute, Second, WeekDay, WeekNum, and more Date & Time Functions are available in DAX. 

Also, Read: Data Visualization with Power BI

Allow me to paint a picture for you. Suppose we have a data set of an e-commerce store from 2013 to 2021. We need to create different visuals to understand the trends and interpret the patterns of the market. Sometimes the data itself is not sufficient to do so. We need to create special measures and calculated columns as per our requirement to analyze deep insights into the data.

In this article, we will look into the different Date and Time functions in Power BI that can be used for data analysis by going through the following pointers-

• How to create a Date Table using different functions
• How to use the TODAY, DATEDIFF, DATEVALUE, DAY Functions
• To use the NOW, HOUR, MINUTE, SECOND, TIME, TIMEVALUE functions
• How to use the EDATE, EOMONTH Functions
• How to use the UTCNOW and UTCTODAY functions
• To use the YEARFRAC function

How to create a Date Table using different functions?

You might be aware that the DAX function may be used to construct an automatic date table, and that several other date and time functions in Power BI can assist you in creating a suitable date dimension table.

Creating Date Table using CALENDER function-

Now, below you can see how I have created this table using multiple Date and Time DAX functions.

So now we will go through each of the Date and Time functions that I have used to create the table-

• CALENDER()→ It gives you a table with only one date column. It takes as arguments the start and end dates

Syntax- CALENDAR(<StartDate>, <EndDate>)

• DATE() → It accepts a year, month, and day argument and provides a DateTime formatted date.

Syntax- DATE (<year>, <month>, <day>)

• YEAR()→ It accepts a date as an argument and returns a four-digit integer year value.

Syntax- YEAR(Date)

• QUARTER()→ It takes a date as an argument and GIVES a number ranging from  1 to 4 as the quarter value.

Syntax- QUARTER(Date)

• MONTH()→ This function accepts a date as an argument and outputs a month number between 1 and 12.

Syntax- MONTH(Date)

• WEEKNUM() → It accepts a date as an argument and, depending on the return type value, returns the week number for the given date and year.

Syntax –WEEKNUM(Date, [ReturnTypeNumber])

where ReturnTypeNumber is either 1 or 2. If it’s a 1, the week starts on Sunday; if it’s a 2, the week starts on Monday.

• WEEKDAY() → It accepts a date as an argument and returns a number between 1 and 7 based on the weekday.

Syntax- WEEKDAY(Date,[ReturnTypeNumber])

where ReturnTypeNumber is a digit(1,2,3) that determines the return value

1 implies Sunday(1) through Saturday(7)

2 implies Monday(1) through Sunday(7)

3 implies Monday(0) through Sunday(6)

Let’s check out the table visual that we can create using this newly created date table.

Creating Date Table using CALENDERAUTO function-

Did you that Power BI uses the CALENDERAUTO function to discover the start and end dates from the data model when date fields exist.

However, there is one drawback. If your data model contains dates that aren’t used for analysis, such as birth dates, it will still choose those. This will result in a far broader date range than you desire.

The CALENDERAUTO function returns a table with one column of dates automatically calculated from the model.

Syntax- CALENDERAUTO ([FiscalYearEndMonth])

Here, the ([FiscalYearEndMonth] is a DAX expression that yields a number ranging between 1 and 12. 

• If you mention a number, it will default to starting at that number and going up to 12. 
• If this argument is left blank, then the calendar table of the current user will be sorted from the lowest to the highest value.

After using some Date and Time Functions to create a table, let us go through some more functions in detail.

How to use the TODAY, DATEDIFF, DATEVALUE, DAY Functions?

The below DAX functions are used to return values related to the dates specified.

TODAY() – The name of this function is self-explanatory. It will only return the current date and not the time.

Syntax- TODAY()

Let me show you a visual representation of the same.

As you can see above, I have created a new column, Today’s Date, in our dataset and depicted it using a card visual.

DATEDIFF() – This function calculates the total amount of time that has passed between two dates. The finest aspect is that the results can be calculated in seconds, minutes, hours, days, weeks, months, quarters, or years.

Syntax- DATEDIFF(Dt1, Dt2, Interval)

where Dt1 and Dt2 are scalar DateTime values.

Interval- This can be ‘SECOND’, ‘MINUTE’, ‘HOUR’, ‘DAY’, ‘WEEK’, ‘MONTH’, ‘QUARTER’, or ‘YEAR’.

You can see in the drop-down list, that there are 8 return types available. I will provide an example for two of these below-

Example1- 

This example shows the DATEDIFF function with DAY as an interval. This means that here time difference is returned based on the number of days.

Example2- 

This example shows the DATEDIFF function with YEAR as an interval. This means that here time difference is returned based on the number of years.

DATEVALUE() – This function takes a date in text format and changes it to a date in date-time format.

Syntax-  DATEVALUE(DateTxt)

where DateTxt is a text that represents a date.

Since I have not specified any year in the text, by default it has taken the current year and displayed it.

DAY() – This function is fairly straightforward. It returns a day number between 1 and 31.

Syntax- DAY(Date)

where the Date should be in date-time format.

You can provide the following as the Date parameter-

• Another date function’s result.
• A date-returning expression.
• A DateTime formatted date.
• A date is represented as text in one of the supported date string formats.

How to use the NOW, HOUR, MINUTE, SECOND, TIME, TIMEVALUE functions?

The below functions help us calculate time from a given hour, minute, and second. 

NOW() – This function returns both the current date and time. 

 Syntax- NOW()

By looking at the above image, I guess you can make out the difference between the NOW() and TODAY() function. . TODAY() returns the current date but with a 12:00:00 default time, whereas NOW() returns both, the current date and time.

HOUR(), MINUTE(), and SECOND() – As the name suggests, these functions extract the hour, minute, and second information from a specified date and time value.

Syntax- HOUR(Datetime) / MINUTE(Datetime) / SECOND(Datetime)

where Datetime is a datetime or text value in a specific time format such as 2:38:54 pm or 14:38:54.

TIME() – When you pass hours, minutes, and seconds as numbers, the TIME function outputs a time in DateTime format.

Syntax- TIME(Hour, Minute, Second)

TIMEVALUE() – If you pass time in text format, this function returns a time in DateTime format.

Syntax- TIMEVALUE(TimeText)

where TimeText is a text that represents a specific time of the day.

Did something catch your eye in the above two Time and Timevalue examples? If not, let me bring it to your notice. As I have not mentioned any particular date in our two queries, the visuals it is displaying the Power BI default date. Quite fascinating right!

How to use the EDATE, EOMONTH Functions?

EDATE() – This function takes two arguments: one is the date, and the other is the number of months. It returns the date that is one month before or after the provided start date.

Syntax-EDATE(StartDate, Months)

EOMONTH() – This function is similar to the EDATE function, except that it returns the last date of the month when the provided number of months before or follows the supplied start date.

Syntax-EOMONTH(StartDate, Months)

How to use the UTCNOW and UTCTODAY functions?

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

But one thing that you should note is that the UTCTODAY function only returns the current UTC date, whereas the UTCNOW function returns the current UTC date and UTC time value.

Syntax- UTCNOW() /UTCTODAY()

How to use the YEARFRAC function?

The number of whole days between two dates is used to compute the fraction of the year represented by this function.

Syntax- YEARFRAC(StartDate, EndDate,<Basis>)

where values for <Basis> are-

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

Also, Read: Power BI Comparison Charts – Shiksha Online

In a Nutshell

This article introduced you to Date and Time functions in Power BI and we discussed their key features. After reading this article, you can go and experiment with these functions and optimize your Data Analytics and Visualization.

Top Trending Tech Articles:
Career Opportunities after BTech Online Python Compiler What is Coding Queue Data Structure Top Programming Language Trending DevOps Tools Highest Paid IT Jobs Most In Demand IT Skills Networking Interview Questions Features of Java Basic Linux Commands Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.