How to Deal with Time Intelligence Functions in DAX — DAX in Power BI — Chapter 7

I have explained the concept of combining DAX expression with the element of time.


A very common term is used in DAX whenever we are building any data model, is Time Intelligence.

It is not only combined and organized data into periods of time but also to understand how each period relates to any other grouping sequentially.

According to Microsoft Document, there are 35 Time Intelligence functions in DAX. These functions enable us to shape data using time periods including days, months, quarters, and years and build comparative calculations over those periods.

In this blog, you are going to explore different Time Intelligence functions with hands-on experience.

Prerequisite Model Requirement

To start with Time Intelligence functions, you need one Date table in your data model. As well as the Date table should meet the following requirements.

  1. A Date column with date/time data type
  2. Unique values for date column
  3. No missing values or blanks
  4. The date column should cover full years

If you want to create one automatic Date table with all required columns. please follow my blog about the date table creation.

Get Data

I consider the US Superstore dataset from Kaggle.

  • 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.
  • In this data model, create one date table with required columns and get ready to explore time intelligence functions.

Basic Time Intelligence Functions

There are two main categorizations for DAX time intelligence functions, one is summarizations over time and another is comparisons over time. Some additionals time intelligence calculations are also available.

Exploring year-to-date, quarter-to-date, and month-to-date

There are two approaches to create measures for summarization over time. 

First, you create one calculated measure of Total Sales. So that, you can create the next set of measures based on this.

Total Sales = SUM(‘US Superstore data’[Sales])

Using CALCULATE function

Image by author

Here you use three functions DATESYTD, DATESQTD, DATESMTD. 

Without CALCULATE function

Now you will use simplified DAX functions to to-date calculations TOTALYTD, TOTALQTD, and TOTALMTD.

Image by Author

There is some similar type of functions like STARTOFYEAR, ENDOFYEAR, OPENINGBALANCEYEAR, and CLOSINGBALANCEYEAR.

Computing time periods from prior periods

These functions are very useful for making comparisons of trends during a time period this year to the same time period last year.

Using SAMEPERIODLASTYEAR

This function is a specialized version. It returns a set of dates shifted one year back in time.

Image by Author

Using DATEADD function

This DATEADD function is generic function which uses the number and type of period to shift. 

Image by Author

Using PARALLELPERIOD

This is similar to DATEADD, it returns the full period in the third parameter instead of the partial period like DATEADD.

Image by Author

There is some similar type of functions like PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH, PREVIOUSDAY, NEXTYEAR, NEXTQUARTER, NEXTMONTH, and NEXTDAY.

Finding a difference over previous periods

Here we will explore one example for the difference over the previous year (YOY). You need some more measures to achieve this. Let us find out the example. 

Image by Author

Calculating a moving annual total

It is another calculation that eliminates seasonal changes in sales amount is the moving annual total (MAT), which means, it considers the aggregation over the past 12 months.

Dividing this MAT value by the number of months included in the period averages it over the time frame, which gives you a moving annual average (MAA).

Image by Author

Download

Please find the code in the below location

DAX — Chapter 7 https://github.com/arpitag1/Power-BI

Video

Conclusion

In this blog, the following things have been captured

  1. Exploring year-to-date, quarter-to-date, and month-to-date
  2. Computing time periods from prior periods
  3. Finding a difference over previous periods

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

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

1 comment

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: