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

Table of Contents

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

***** Related Links *****
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.

Advanced Analytics in Power BI

Good luck!

Sam

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginnerâ€™s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...