Time Intelligence DAX Functions In Power BI

For this tutorial, we’ll branch out to some examples of time intelligence calculations using a combination of DAX functions.

This kind of calculation is useful when you want to compare your business data over a certain period. You can do it by days, months, quarters, or years.

Aside from the CALCULATE function, using other DAX functions can greatly help in building great time-related analysis.

I’ll introduce you to some of the most common DAX functions used for this kind of calculation.

Using Time Intelligence DAX Functions

In this example, I’ll teach you how to create a new measure to compare the current sales to last year. Specifically, we will compare the sales of a specific day from the previous and current year.

Let’s call this measure as Sales LY and start it off with the CALCULATE function. The CALCULATE function can change the context of any calculation.

Next, we need to put an expression, which in this case, is the Total Sales. Since we need to change the context to compare the sales, we’ll use the SAMEPERIODLASTYEAR function. This function returns a set of dates in the current selection from the previous year.

Lastly, we need to reference the Dates column from the Date table.

time intelligence dax

In the table, we can already see the current sales data from the Total Sales column. When we bring the Sales LY measure to the table, we’ll be able to see the total sales from last year. The Sales LY table still calculates the total sales, however, the date context is changed to the previous year.

For instance, on the 13th of October, 2018, the Total Sales is $153,058.20.

If we check the Sales LY data for the 13th of October, 2019, we’ll see that it is also $153,058.20.

With the help of the CALCULATE function, the formula changes the initial context to look back on the sales of Oct. 13, 2018. This is a simple example of a time intelligence DAX calculation.

Creating A New Measure Group

If we look at our Key Measures group in the model, you’ll notice that our measures are starting to pile up. Since we’re branching out into a new calculation, it’s best to create a new measure group. This way, we can optimize and clean our Power BI.

To do that, click the Home ribbon, and then click Enter Data.

Since the measures are about time intelligence calculations, we can name the new measure group as Time Comparison. After renaming, click Load.

Then, we can now move the related time intelligence DAX measures into the new table.

Also, we can make the Sales LY measure as the main measure. To do that, click the Modeling ribbon, and then click Home Table. From here, we can change the default home table from Key Measures to Time Comparison.

We can see that the Sales LY measure is now placed under the Time Comparison measure group.

time intelligence dax

Furthermore, we have to make sure that the Time Comparison table is placed near the Key Measures table.

This is the ideal set up of the data model since the two additional tables are just for organizational purposes. They are not part of the main model structure for data relationships.

Using Advanced Time Intelligence DAX Functions

We now have the Total Sales and Sales LY data in our table. We can branch out to advanced time intelligence calculations using these core measures and other DAX functions.

In this example, we will calculate for Sales Difference Year on Year. In the formula, we simply have to find the difference between the Total Sales and Sales LY.

time intelligence dax

To see the results, we just have to bring the Sales Diff. YoY measure into the table. We can also turn this table into a visualization like this.

time intelligence dax

These are just simple visualizations. However, if we add slicers for months or weeks, the visualization will look more interesting.

In this other example, I’ll teach you another time intelligence calculation using a different DAX function. This time, let’s use the DATEADD function to calculate Sales Last Year.

Using the original formula, we just need to change the SAMEPERIODLASTYEAR to DATEADD. After referencing the Date table, enter -1 as the number of interval.

time intelligence dax

The DATEADD function is very flexible because we can go by day, month, quarter, or year.

Since we’re going by year, the formula will calculate the total sales from the previous year. Obviously, the results will be the same as before because we just used a different formula.

Lastly, we can simply copy and paste the existing measure and just change the name. For example, from Sales LY to Profits LY. Instead of Total Sales, we’ll reference the Total Profits measure.

time intelligence dax

That’s how we can see the profits from last year.

We can branch out to more analysis from here, like the current year’s profit. We can even work out the percentage difference year on year.  

The key to a great time intelligence calculation is to use a combination of DAX functions.

***** Related Links *****
Common Time Intelligence Patterns Used In Power BI
The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI
Time Intelligence In Power BI: How to Calculate The Number of Transactions Made in the Last N Days


These time intelligence examples may be simple, but it’s a great application of all the things that we have discussed so far. We applied the concepts of initial context, iterating functions, aggregating functions, measure branching, and the CALCULATE function.

I hope you’ve learned a lot of techniques and DAX functions. It will be great if you can apply them to your reports as soon as you can.


Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More