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

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

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Cohort Analysis - DAX

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 🙂



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

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
OwenAuger Super Contributor
Super Contributor

Re: Cohort Analysis - DAX

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 🙂



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

Proud to be a Datanaut!




View solution in original post

stfox Regular Visitor
Regular Visitor

Re: Cohort Analysis - DAX

Excellent! Thanks Owen. Just what I was after. 

Re: Cohort Analysis - DAX

where is the signup month formula?

GershwinMunich Frequent Visitor
Frequent Visitor

Re: Cohort Analysis - DAX

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 173 members 1,766 guests
Please welcome our newest community members: