# How To Implement Cohort Analysis In Power BI – Advanced DAX Concepts

In this blog, I am introducing Power BI cohort analysis. This was one of the topics that I went through in detail in a Learning Summit, where I demonstrated what cohort analysis is and how you can do it in Power BI. In this tutorial, you will learn how to set it up inside your Power BI model. You may watch the full video of this tutorial at the bottom of this blog.

The idea around cohort analysis is that we group our customers based on a particular behavior or attribute that they have.

In this example, we’re grouping our customers based on when they first initially purchased, and then we’re going to analyze the retention rate of those customers.

So the idea is to create a cohort of when the customers first purchase and then analyzed through time how long it takes for them to come and purchase again. We then compare all the customers in our cohort of all the months and see whether the retention levels improve or reduce.

In this demonstration, we have percentages, which is a good way of looking at it, but we also have absolute numbers as well.

## Classifying Customer Purchases

If you think about cohort by itself, it means “grouping of customers”.  So here we work out when the customer joined or when they first purchased from us. This is what we’re classifying as the Join Date

The Join Date formula is just the Min Purchase Date in the Sales table. So we’re working out the minimum or the lowest date that any of these customers at each different row has purchased.

Then we classified them in their particular cohort based on what month and year they have purchased

For example, Jesse Evans here purchased on March 11, 2014, so customers like Evans will be in the March 2014 cohort.

## DAX Formulas For Cohort Analysis

The formulas we use here are very simple. The Join Date is just the MIN of the Order Date (Sales).

The Cohort Month is basically finding out the month of that Join Date.

And then, we bring them here.

We can create a cohort on many different variables.  This is just one real world example of retention rates.

## Classifying Time Frames

The other thing to classify is the time periods. These are just generic (1, 2, 3, etc.), but we need to create some generic time periods to compare all these cohorts (Jan 2014, Feb 2014, etc.) because they’re all in different time frames. We need to do a like-for-like comparison of all these cohorts over different time periods.

So this is how we set it up if we’re doing this sort of analysis. We have Period 1 to 12, and we’re capturing Min Days and Max Days.

For instance, if a customer purchased from us and then purchased back again within the first 30 days, that customer would be classified as the first retention period (Period 1). If the customer purchased from us again between 120 and 150 days, that customer is in Period 5, and so on.

We see that this is being completed across all our Cohorts even though these customers are making their initial purchase at a later time. We then compare our cohort on a like-for-like basis all the way back to January 2014 by utilizing the complex combination of calculations.

## Conclusion

In the Learning Summit I dove much further into how exactly you can generate your formulas to be able to do this, but I thought that this would be a good introduction. Many of you probably even haven’t heard of cohort analysis.

This is quite an advanced analytical technique, but I wanted to give you a primer of what cohort analysis is and show you how we can figure this out in Power BI. We can also do it in a very dynamic and effective way where we can integrate it into our model.

I’ve seen this analysis done in Excel and some other advanced tools, but we can do it in Power Bi and make it even better and more efficient. Check out the links below for more Power BI resources around this topic.

Cheers!

Sam

## Optimizing DAX: Performance Tips for Power BI Reports

Data analysis in Power BI is not only about creating visually appealing reports but also about ensuring...

## DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.

## Introduction to the RANKX and TOPN DAX functions in Power BI

One of the key aspects of analyzing data is to be able to rank or compare different elements based on...

## Power BI Visualization Technique: Learn How To Create Background Design Plates

Here's a Power BI visualization technique that you can utilize within your reports by using large...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculate Average Per Customer Transaction Using DAX In Power BI

What I want to demonstrate in this tutorial is how we can calculate average sales, profits, or...

## Round Bar Chart – A Visualization For Your Dashboard

In this tutorial, you’ll learn how to create round bar charts for your Power BI report. It’s another...

## Conditional Formatting with Transparency Hex Codes

One of the best ways to implement conditional formatting is using the Field value option in the...

## Custom Theming In Power BI

For today's post, I'll do a quick review of the customizations you can make by directly editing and...

## Power BI Automation With Elgato Stream Deck

Today, I'm going to walk you through an outstanding Power BI automation using Elgato Stream Deck. We're...

## How To Create Profit And Loss (P&L) Statements In Power BI

In this tutorial, I’ll be teaching the Profit and Loss, or P&L statement, in Power BI. You may...

## Power BI Project Planning: Discovery & Ingestion

In today's blog post, we'll be continuing our series on how you can plan for your Power BI project....