# Time Intelligence In Power BI: How to Calculate The Number of Transactions Made in the Last N Days

Sometimes when analyzing your Power BI reports, you may want to know how many transactions have occurred over a particular period of time. In this blog post, I’ll show you exactly how to calculate transactions within a given period of days using time intelligence in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

This post will cover a high quality piece of analytical work that can be efficiently executed in Power BI. We’ll be using a combination of DAX formulas around date tables to extract useful insights.

## Determining Transactions In The Last N Days

We will look at how many transactions any particular customer has made in the last N days. There’s actually a couple of ways to go about this.

## Total Number Of Days Transacting

To find out the number of days a customer has transacted with us, we can create a measure using this formula:

Let’s say for instance a customer bought 3 different individual products in one particular day. If this is the formula we use, it is going to return just 1 transaction (and not 3) because we’re going with distinct count of the purchase date.

## Total Transactions

But if we want to work out the total number of transactions regardless of the days, we can use the COUNTROWS function in the sales table.

This is going to give us individual transactions. If a customer bought 3 different products in one day, then this is going to be 3 transactions.

When looking at the sales table, we might notice there’s not much of a difference between the Total Days Transacting and the Total Transactions columns. But there will be in some cases, like with Carlos Scott who had multiple transactions in one day.

## Transactions In The Last 60 Days

What we want to work out next is how many transactions have been made in the last N of days, or the last 60 days in the case of our demo data set.

We will be isolating how many transactions have been made in a particular time period versus in total.

Remember that this is a dynamic calculation so as we move through time, that 60 days is going to move with us. We’re only going to look at 60 days backwards.

So to come up with this formula, we used a combination of formula techniques and function. We’ll use variables here and with functions we’ll work with CALCULATE, and FILTER. Let’s take a look at part here.

## 1. Using Variables (VAR)

We’ll use VARIABLES to come up with the last sales date of purchases (in the current context). Since this is a demo data set, I used LASTDATE to give us a date to be based off.

Ordinarily, you won’t be using this formula since you will be looking at a live data set. You will be fine using this formula instead if that’s the case:

## 2. Working with the CALCULATE function

In this instance, I have used the COUNTROWS formula. But in theory, we can also use the Total Transactions measure.

We’re still counting the number of transactions, but we’re just changing the context in terms of how we’re calculating it.

This is exactly what the CALCULATE function does; we’ll be looking at the total transactions at a window of the last 60 days.

## 3. The FILTER function

The third step is to filter the date table for the last 60 days by using the FILTER function. This particular function will open up a new window to calculate the total transactions in any particular time range.

With ALL(DATES), we will be releasing any filters that may be coming from the date table initially. Then we’ll add the filters back by iterating through every single date and evaluating if it comes between the (Last Date – 60) and the Last Date.

## Using Time Intelligence in Power BI To Improve Your Sales & Marketing

The end result is really some powerful stuff. We can reuse this to create a new measure. For example, we can recalculate the formula to up to 180 days like below:

We can then drag the results to the canvas and branch out the table to determine the velocity of a customer’s purchasing cycle.

We can analyze the sales table and then ask ourselves the tough questions. Why are our customers transacting more in the last 60 days than in the last 180 days?

Was it because we gave them a special discount, or was it because of a marketing outreach we did? Once we have the answers, we can use them to improve on our marketing strategies.

Analyzing transactional frequency is crucial for a business, specially if you are a high-frequency retailer or running an online company.

Show Last N Sales Of A Customer Only Using Power BI
Cumulative Totals Across Averages & Dynamic Date Logic – Advanced DAX
Show Results Up To Current Date Or A Specific Date In Power BI

## Conclusion

The insights you can derive from time intelligence in Power BI can drive many actions and decisions with your marketing, inventory, and financial initiatives.

The technique can be used in so many different ways in the right environment. Remember that it doesn’t always have to be transactions; it can be sales, costs, profits, etc. that could be reviewed using the same exact technique I showcased.

If you can learn how to incorporate this in your reports, you’ll be developing high-quality work that your customers and organization will value.

To learn even more advanced analytical techniques for Power BI, check out the module link below from Enterprise DNA Online.

Good luck!

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...

## Format Data In Power BI: Addressing Irregular Data Formats

In today's blog post, we'll discuss Problem of the Week #6. I'll show you how to format data in Power...

## Calculating A Rolling Average In Power BI Using DAX

In this tutorial, I’m going to show you how to calculate a dynamic Rolling Average in Power BI using...

## Paginated Report In Power BI: An Introduction

In this tutorial, you’ll learn about paginated reports in Power BI. A paginated report is another name...

## Creating Measure Tables & Subfolders In Power BI

For today's blog, I'll cover measure tables and subfolders. We're going to set up measure tables, and...

## Ultimate Guide To Multiple IF Statements In Power BI

If you are looking to create more complex logic, then using multiple if statements in Power BI is a...

## Highlight Highest & Lowest Values Using Quick Measure In Power BI

Quick Measures Pro is a powerful external tool to streamline data analysis and visualization in Power...

## Power BI Desktop Update: The Charticulator Visual

Today, I would like to go over something that I found out by scrolling through Twitter. What I have...

## How To Build a Portfolio And Showcase Your Data Projects to Employers

In today’s data-driven world, the ability to work with data has become an essential skill. Whether...

## New vs Existing Customers – Advanced Analytics In Power BI

If you’re an online retailer or a high frequency sales operation, then understanding your customer...