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.
- A Date column with date/time data type
- Unique values for date column
- No missing values or blanks
- 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.
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
- 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
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.
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.
This function is a specialized version. It returns a set of dates shifted one year back in time.
Using DATEADD function
This DATEADD function is generic function which uses the number and type of period to shift.
This is similar to DATEADD, it returns the full period in the third parameter instead of the partial period like DATEADD.
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.
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).
Please find the code in the below location
DAX — Chapter 7 https://github.com/arpitag1/Power-BI
In this blog, the following things have been captured
- Exploring year-to-date, quarter-to-date, and month-to-date
- Computing time periods from prior periods
- 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.