cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Cohort Analysis Saas

Hi,

 

I have a challenge, I need to create a Cohort Analysis for Saas company.

 

There are two tables:

 

Tables.png

 

Both linked by a subscription_id.

 

In one table, there is the register_date (dd/mm/YYYY) of each user and in the other table, there is the subscription_start_date (dd/mm/YYYY) that is the date that the user starts paying (not all users start paying).

 

Right now I have this table:

 

Tabla Basica 1.png

 

And I need to create this table two tables:

 

-  Table one where:

  • The first column is the month of register,
  • The second column is the number of registered users on that month
  • The third column is the number of registered users on the month of the first column who starts paying in his first month (month 0)
  • The fourth column is the number of registered users on the month of the first column who starts paying in second month (month 1) plus the users paying in previous months, in this case month 0.
  • The next columns work the same than the fourth column just adding paying users month after month 

Tabla 1.png

 

In the second table is the same information just getting the percentage (paying users / users registers) ex:

 

- Ago-19 and month 3: 138/1214=11,37%

Tabla 2.png

 

Many thanks for your help, I really appreciated

 

Best

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Cohort Analysis Saas

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Cohort Analysis Saas

@diegogoya86 Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IX
Super User IX

Re: Cohort Analysis Saas

Highlighted
Community Support
Community Support

Re: Cohort Analysis Saas

Hi @diegogoya86,

I think you can create a new table to extract all column types with the corresponding index, then you can create a matrix table with use new table column and row table rows categories.
After these steps, you can add two measures: one of them uses the current data label of row/column to lookup and summary values; the other one checks the previous measure result and setting up conditional color formatting based on 'summary value' measure.

If you are confused about coding formula, please share some dummy data to test.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors