cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stfox
Helper I
Helper I

Cohort Analysis - DAX

Hi Folks

I want to create a Cohort Analysis. This involves calculating the the percentage of a cohort that is left in each of the subsequent months  following an initial signup. The screenshot below demonstrates the basic concept.  I want to avoild hard coding any date filters as a new cohort is added each month. 

 

I am thinking the solution involves a CALCULATE formula, but I am getting tripped up with the DAX to identify the denominator in the % calculation (the starting number of customers in each Cohort (40 in the first cohort, 35 in the second 30  in the 3rd cohort).

Can anyone help me out with the DAX?

 

 

Any help is much appreciated.  Excel/ PowerPivot File attached. 

 

Thanks Steve 

 

Cohort.PNGPowerPivot file

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi Steve,

 

Here are a couple of ideas I can think of depending on the logic you want to apply (they give the same answer using your sample data).

 

These are additive over signup month (which you may want) and invoice month (which you may not want). You can modify that behaviour if needed.

 

1. Denominator = For each Signup Month, Number of customers where Invoice Month = Signup Month

=
DIVIDE (
    [Count of Customer ID],
    CALCULATE (
        [Count of Customer ID],
        GENERATE (
            VALUES ( Table1[Signup Month] ),
            FILTER (
                ALL ( Table1[Invoice_Month] ),
                Table1[Invoice_Month] = Table1[Signup Month]
            )
        )
    )
)

 

2. Denominator = For each Signup Month, Number of customers in first Invoice Month with nonblank customers

=
DIVIDE (
    [Count of Customer ID],
    CALCULATE (
        [Count of Customer ID],
        GENERATE (
            VALUES ( Table1[Signup Month] ),
            FIRSTNONBLANK ( ALL ( Table1[Invoice_Month] ), [Count of Customer ID] )
        )
    )
)

Owen 🙂


Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

Hi Steve,

 

Here are a couple of ideas I can think of depending on the logic you want to apply (they give the same answer using your sample data).

 

These are additive over signup month (which you may want) and invoice month (which you may not want). You can modify that behaviour if needed.

 

1. Denominator = For each Signup Month, Number of customers where Invoice Month = Signup Month

=
DIVIDE (
    [Count of Customer ID],
    CALCULATE (
        [Count of Customer ID],
        GENERATE (
            VALUES ( Table1[Signup Month] ),
            FILTER (
                ALL ( Table1[Invoice_Month] ),
                Table1[Invoice_Month] = Table1[Signup Month]
            )
        )
    )
)

 

2. Denominator = For each Signup Month, Number of customers in first Invoice Month with nonblank customers

=
DIVIDE (
    [Count of Customer ID],
    CALCULATE (
        [Count of Customer ID],
        GENERATE (
            VALUES ( Table1[Signup Month] ),
            FIRSTNONBLANK ( ALL ( Table1[Invoice_Month] ), [Count of Customer ID] )
        )
    )
)

Owen 🙂


Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

this formula does not work. it says too few arguments are trying to pass the filter function

where is the signup month formula?

Excellent! Thanks Owen. Just what I was after. 

Hi @stfox @OwenAuger 

would you mind to share the solution in pbix format? Because these solutions doesn't work in my model. What data do you use for the matrix's column and row header?

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!