# 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

## Sort Legend Power BI Feature Tutorial And Review

As part of a mini-series that I’m doing on Power BI’s November 2021 updates, I’d like to tackle the...

## Power BI Customer Segmentation: Showcasing Group Movement Through Time

This tutorial is all about customer insights. I will demonstrate an advanced Power BI customer...

## Compare Multiple Metrics Cumulatively In Power BI Using Advanced DAX

I’ve previously showcased how you can compare your actual results versus your budgeted results using...

## Python Dataset: Applying Repeatable Codes

In this blog, you’ll learn how to apply repeatable codes or functions to Python datasets to produce the...

## What Is Power BI Used For?

In today's dynamic and data-driven business landscape, organizations require robust tools such as Power...

## Power Automate Development: Best Practices To Implement

Power Automate can seem intimidating at first, but gradually, it becomes easier. Part of learning this...

## Stored Procedures In SQL | An Overview

In this blog, we’re going to discuss stored procedures in SQL that you can utilize to save a set of...

## How To Date Harvest In Power BI Using DAX

In this tutorial, we'll go through date harvesting in Power BI to get information from a date slicer...

## Tips For Power BI Report Design – Best Practices

It's important to make your visuals look great to have a compelling Power BI report. In this tutorial,...

## Simple Power BI Measures – Beginners’ Tutorial and Best Practices

I'm going to show you some simple Power BI measures you can start with. These can give you a good...

## DAX MAXX Function In Power BI – Discover Last Date

I’m going to share a quick and efficient formula around the DAX MAXX function in Power BI. In this...

## KPI Ticker In A Power BI Report | PBI Custom Visuals

For this blog tutorial, I’d like to go through a custom visual I used in the report I developed for my...