Blog

You are currently viewing Date and Time Function in PowerBI

Date and Time Function in PowerBI

Loading

In this blog, we will perform different Date and Time Function in PowerBI. Then we can get the required and importance of following functions:

Introduction

Date and time functions in PowerBI enable advanced manipulation and analysis of date-time data. Functions like DATE, TODAY, NOW, YEAR, MONTH, DATEDIFF, and FORMAT allow users to create dynamic calculations, filter data by periods, and generate insights such as trends, durations, and time-based aggregations in reports.

Dataset Description

This user dataset includes unique user IDs, usernames, emails, dates of birth, and account creation timestamps, allowing for user analysis by demographics, registration dates, and identification details.
The link to download the dataset is here.

Steps to perform different Date and Time Function in PowerBI:

Step 1. Open Power BI Desktop

Step 2. Click on Get Data to select Text/CSV in Home tab

Step 3. Browse csv file and hit Open

Step 4. A window will open to Transform Data and Load, click on Transform Data

5001.PNG

Now, let’s dive into date/time functions:

Date.From:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.From`and following formula:

= Date.From([created_at])

5002.PNG

Date.Year:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.Year` and following formula:

= Date.Year([created_at])

5002.PNG

Date.Month:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.Month` and following formula:

= Date.Month([created_at])

5002.PNG

Date.Day:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.Day` and following formula:

= Date.Day([created_at])

5002.PNG

Date.DayOfWeek:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.DayOfWeek` and following formula:

= Date.DayOfWeek([created_at])

5002.PNG

Date.DayOfYear:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.DayOfYear` and following formula:

= Date.DayOfYear([created_at])

5002.PNG

Date.QuarterOfYear:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.QuarterOfYear` and following formula:

= Date.QuarterOfYear([created_at])

5002.PNG

Date.StartOfYear:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.StartOfYear` and following formula:

= Date.StartOfYear([created_at])

5002.PNG

Date.EndOfYear:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.EndOfYear` and following formula:

= Date.EndOfYear([created_at])

5002.PNG

Date.StartOfMonth:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.StartOfMonth` and following formula:

= Date.StartOfMonth([created_at])

5002.PNG

Date.EndOfMonth:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.EndOfMonth` and following formula:

= Date.EndOfMonth([created_at])

5002.PNG

Date.StartOfWeek:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.StartOfWeek` and following formula:

= Date.StartOfWeek([created_at])

5002.PNG

Date.EndOfWeek:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.EndOfWeek` and following formula:

= Date.EndOfWeek([created_at])

5002.PNG

Date.AddYears:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.AddYears` and following formula:

= Date.AddYears([created_at], 1)

5002.PNG

Date.AddMonths:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.AddMonths` and following formula:

= Date.AddMonths([created_at], 1)

5002.PNG

Date.AddDays:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Date.AddDays` and following formula:

= Date.AddDays([created_at], 10)

5002.PNG

Time.From:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Time.From` and following formula:

= Time.From([created_at])

5002.PNG

Time.Hour:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Time.Hour` and following formula:

= Time.Hour([created_at])

5002.PNG

Time.Minute:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Time.Minute` and following formula:

= Time.Minute([created_at])

5002.PNG

Time.Second:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Time.Second` and following formula:

= Time.Second([created_at])

5002.PNG

Time.StartOfHour:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Time.StartOfHour` and following formula:

= Time.StartOfHour([created_at])

5002.PNG

Time.EndOfHour:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Time.EndOfHour` and following formula:

= Time.EndOfHour([created_at])

5002.PNG

DateTime.From:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTime.From` and following formula:

= DateTime.From([created_at])

5002.PNG

DateTime.LocalNow:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTime.LocalNow` and following formula:

= DateTime.LocalNow()

5002.PNG

DateTimeZone.UtcNow:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTimeZone.UtcNow` and following formula:

= DateTimeZone.UtcNow()

5002.PNG

DateTime.Date:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTime.Date` and following formula:

= DateTime.Date([created_at])

5002.PNG

DateTime.Time:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTime.Time` and following formula:

= DateTime.Time([created_at])

5002.PNG

DateTime.ToText:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTime.ToText` and following formula:

= DateTime.ToText([created_at], “yyyy-MM-dd HH:mm:ss”)

5002.PNG

DateTime.FromText:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTime.FromText` and following formula:

= DateTime.FromText([DateTime.ToText])

5002.PNG

DateTime.ToRecord:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `DateTime.ToRecord` and following formula:

= DateTime.ToRecord([created_at])

5002.PNG

Duration.From:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Duration.From` and following formula:

= Duration.From(DateTime.LocalNow() – [created_at])

5002.PNG

Duration.Days:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Duration.Days` and following formula:

= Duration.Days(DateTime.LocalNow() – [created_at])

5002.PNG

Duration.Hours:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Duration.Hours` and following formula:

= Duration.Hours(DateTime.LocalNow() – [created_at])

5002.PNG

Duration.Minutes:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Duration.Minutes` and following formula:

= Duration.Minutes(DateTime.LocalNow() – [created_at])

5002.PNG

Duration.Seconds:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Duration.Seconds` and following formula:

= Duration.Seconds(DateTime.LocalNow() – [created_at])

5002.PNG

Duration.ToText:

Step 1. Go to Add Column tab and select Custom Column

4019.PNG

Step 2. Type column name `Duration.ToText` and following formula:

= Duration.ToText(DateTime.LocalNow() – [created_at])

5002.PNG

Now the Final Step after performing above all functions:

Step Final: Go to Home tab and hit Close & Apply

5039.PNG

Conclusion

Date and Time function plays important role in data analytics when we need to track event or find pattern on time.

In this documentation, we studied that how Date and Time function used and what it does. There are lots of function in Date, Time, DateTime, DateTimeZone, Duration in Power BI.

If you like this blog, you can share it with your friends or colleague. You can connect with me on social media profiles like LinkedIn, Twitter, and Instagram. If you have any doubt feel free to comment.