# 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.

## InfoPath: Explained With Examples

Are you tired of drowning in a sea of paperwork and struggling to keep your data organized? Enter...

## Retail Management & Demand Forecasting Reports In Power BI

In this Power BI Showcase, we focus on reports that provide analysis on retail management,...

## First N Business Days Revisited – A DAX Coding Language Solution

Let's take another look at the problem discussed in this tutorial, which dynamically compared the first...

## Showcasing Multiple Selections In A Power BI Slicer

Many of you may know that we regularly conduct Power BI Challenges. There are lots of techniques that I...

## Microsoft Flows: Editing And Tracking In Power Automate

Once you understand how a Flow Diagram looks like, it will be easier to make some changes to it. In...

## Creating Power BI Reports Effectively & Avoiding Hidden Pitfalls

In this tutorial, I will discuss four hidden pitfalls in Power BI that can wreck your data model and/or...

## Power BI Page Navigation Buttons

The Power BI page navigation buttons play a critical role when it comes to storytelling. An organized...

## Power BI Report Examples And Best Practices – Part 1

In today's post, I'd like to present some Power BI report examples and best practices. In my own Power...

## Power BI Python Tutorial: How To Translate Texts

This blog will demonstrate how to perform language or text translation using Python and pipe it over...

## Measure In Power BI: Optimization Tips And Techniques

In this tutorial, youâ€™ll learn how to optimize a measure in Power BI. Optimizing measures in your...

## How To Use Power Query Row And Column Selection

This tutorial will discuss how to use selection and projection inside the Power Query Editor. Selection...

## Use Tabular Editor To Create Calculation Groups In Power BI

Today's blog post will give you an introduction to calculation groups. I'll try to answer four basic...