Basic Time and date functions in Power BI

In this blog post, we will learn one of the most important part of DAX: time and date functions. These functions allow you to work with dates and times in various ways, such as extracting, formatting, aggregating, comparing, and filtering. You can use these functions to create dynamic and interactive reports that show trends, patterns, and insights over time.

Time and date functions

Time and date functions are DAX functions that deal with dates and times. They can take values from a column containing date or time and give single value or a table as an output. Time and date functions are useful for several reasons:

  • create calculations based on dates and times
  • format and display dates and times in different ways
  • compare and filter dates and times such as finding the latest or earliest date

How to create a date table in Power BI?

A date table is a special table that contains a list of dates, such as year, month, quarter, week, day, etc. It is essential for time and date calculations in Power BI. There are two ways to create a date table in Power BI:

Option 1: CALENDARAUTO function, which returns a table with a single column named “Date” that contains a contiguous set of dates based on the data in your model considering first date of the starting year till the last date of input in the data. You can also specify the fiscal year end month as an optional argument inside the brackets. 

For example, in the sample excel file we have earliest date of entry as 02-04-2017 and the latest date is 01-04-2020. So if we use the following formula it creates a date table with starting date as 01-01-2017 and last date in date table as 31/12/2020 , even if our data has 01-04-2020 as last date of entry.
DateTable = CALENDARAUTO()
But, if we specify the fiscal year end month DateTable = CALENDARAUTO(6), then in the final data table we will have the last date as 30-06-2020.


Option 2: CALENDAR function, which gives you more control and flexibility over the date table. . For example, the following formula creates a date table with a custom date range.
DateTable = 
    CALENDAR (DATE(2017, 1, 1), DATE(2020, 12, 31))

NOTE: After creating a date table, you need to mark it as a date table in Power BI. The table must be marked as a Date table in the model (via the Mark as Date Table setting), in case the relationship between the Date table and any other table is not based on the Date. You can do this by following these steps:

  • Go to the Model view in Power BI Desktop
  • Select the date table in the Fields pane
  • Click on the Table Tools tab in the ribbon
  • Click on the Mark as Date Table button
  • Select the date column that contains unique and contiguous dates
  • Click OK

Some common time and date functions

Time and date functions can be used for various purposes. You can find the full list of time and date functions and their descriptions on the Microsoft Docs website. In this blog post, we will learn some of the common time and date functions that you can use in Power BI.

Extracting Functions are used to extract a specific part of a date or a time, such as year, month, day, hour, minute, second, etc. Some of the extracting functions are:

FUNCTION DESCRIPTION SYNTAX
YEAR Returns the year of a date as a four-digit integer in the range 1900-9999 YEAR(<datetime>)
MONTH Returns the month of a date as a number from 1 (January) to 12 (December) MONTH(<datetime>)
QUARTER Returns a number from 1 to 4, i.e., the quarter of the year that the date falls into.
1: (January – March)
2: (April – June)
3: (July – September)
4: (October – December)		 QUARTER(<datetime>)
DAY Returns the day of the month of a date as a number from 1 to 31 DAY(<datetime>)
WEEKDAY Returns the day of the week of a date as a number from 1 (Sunday) to 7 (Saturday)

Note: If you want to have weekday name you can use FORMAT Function
WeekdayName = FORMAT(WEEKDAY('DATE'[Date]), "dddd")		 WEEKDAY(<datetime>, [<return_type>])

Return Types:
1 or omitted: Sunday is considered the first day of the week by default.
2: Monday is considered the first day of the week.
And so on
WEEKNUM Returns the week number of a date according to the ISO 8601 standard or a custom return type

 WEEKNUM(<date> [, <return_type>])

Return Types:
System 1: The week containing January 1 is the first week of the year and is numbered week 1.
System 2: The week containing the first Thursday of the year is the first week of the year and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.
HOUR Returns the hour of a time as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.) HOUR(<datetime>)
MINUTE Returns the minute of a time as a number from 0 to 59 MINUTE(<datetime>)
SECOND Returns the second of a time as a number from 0 to 59 SECOND(<datetime>)

Most of these extraction functions can also be done in Power Query when you import a data. You just have to select the column that has date values and then go to ADD COLUMN and then select the data that you want to extract like (Year, Month, Quarter, etc.) 

Comparing functions

Comparing functions are used to compare two dates or times and return a logical value (true or false) or a numeric value (positive, negative, or zero). Some of the comparing functions are:

  • =: Returns true if two values are equal, and false otherwise
  • <>: Returns true if two values are not equal, and false otherwise
  • >: Returns true if the first value is greater than the second value, and false otherwise
  • <: Returns true if the first value is less than the second value, and false otherwise
  • >=: Returns true if the first value is greater than or equal to the second value, and false otherwise
  • <=: Returns true if the first value is less than or equal to the second value, and false otherwise

For example, the following formula returns true if the date column in the Sales table is greater than Last date for DiscountPrice date column, and false otherwise:

Sales After Discount Date = Sales[SalesDate]>Sales[Last Date for DiscountPrice]

Additional Time and Date Functions
DATE: Returns a date in datetime format from a given year, month, and day

TIME: Returns a time in datetime format from a given hour, minute, and second

FORMAT: Returns a text value formatted according to a given format string

DATEVALUE: Converts a date in text format to a date in datetime format

TIMEVALUE: Converts a time in text format to a time in datetime format

NOW: Returns the current date and time in datetime format. (value updated each time you open the worksheet)

TODAY: Returns the current date and the time value is 12:00:00 PM for all dates.(value is not updated each time you open the worksheet, unless you change the settings)

You can notice the difference between Now() and TODAY() Function

NETWORKDAYS: Returns the number of whole workdays between two dates.


DATEDIFF: Returns the number of units (unit specified in Interval, such as days, months, years, etc.) between the input two dates.


I hope this blog post is helpful and informative for you. I have created another detailed blog post for more time intelligence functions which is not covered in this blog post. Thank you for reading!

Comments

Post a Comment

Popular posts from this blog

XLOOKUP Function in Excel

Image
In our previous blog post we have already learned about 2 most useful lookup functions: VLOOKUP and HLOOKUP . In this blog post, we’ll explore what XLOOKUP is, its syntax, why you should use it, its pros and cons, and provide some practical examples. The  XLOOKUP  function revolutionize the way we search and retrieve data. XLOOKUP function is a modern and versatile replacement for traditional lookup functions in Excel. It allows you to find things in a table or range by row, regardless of whether the return column is on the left or right side. It’s like a search engine for your Excel data. Here’s what you need to know: Syntax : =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Arguments : lookup_value : The value you want to search for. lookup_array : The range or array to search. return_array : The range or array from which to return results. [ if_not_found ] : (Optional) Specify what to return if no match is found. [ match_...
Read more

Introduction to Pivot Table in Excel - Advanced

Image
In the previous blog post, we learned the  Basics of Pivot Table  in Excel. In this blog post, we will explore some of the advanced features of pivot tables, such as slicers, timelines, calculated fields, pivot charts, managing relationships, etc. These features can help you change your existing pivot table and create different views of your data every time. Slicers Slicers are used to filter your data by selecting specific values from a field. For example in this data table , we have a column named Product (Crayons, Geometry Box, Paper, Pen, Stapler, Sticky Notes)  you can use a slicer to show only the data for a particular product or products.  Steps to add a Slicer: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the field for the slicer. A slicer window will appear, where you can select a field and a slicer will be inserted. You can select one option or multiselect by holding down the CTRL key while making your selections. You c...
Read more

Introduction to Pivot Table in Excel - Basics

Image
Pivot tables are one of the most powerful and useful features in Excel. Allowing us to quickly summarize, analyze, and explore large amounts of data in various ways. It is a special type of table that displays the summary of a data set and helps us in analyzing the data in seconds without knowing any formulas and functions. It consists of four areas: rows, columns, values, and filters. You can drag and drop different fields from your data set into these areas to create different views of your data. In this blog post, we will introduce the basics of pivot tables. Converting data to tabular data before inserting Pivot Table is an added advantage as this eliminates the need to frequently adjust the table range as new entries are added (select a cell from the data and click CTRL+T and then OK).  Additionally the following conditions need to be fulfilled: ✔ Each column should have unique headers, and the headers should be descriptive enough.       For ex. If Revenue...
Read more