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