Forum Discussion

Bharani308's avatar
Bharani308
Frequent Visitor
6 years ago

Help Required on Cohort Analysis Table

Hello People,

 

I am trying to do a cohort analysis with below data but I did not get the desired result

 

Student IdApplication Sent DateDeposit Paid
15456308/07/201917 July 2019
14313917/07/201929 July 2019
14290127/07/201930 July 2019
15004110/07/201907 August 2019
15004103/07/201909 August 2019
16241717/07/201909 August 2019
14980325/07/201913 August 2019
14908929/07/201921 August 2019
14147326/07/201922 August 2019
16194129/07/201927 August 2019
14885131/07/201929 August 2019
16217922/07/201902 September 2019
14908929/07/201906 September 2019
14171109/07/201912 September 2019
14956525/07/201917 September 2019
14147325/07/201924 September 2019
14932726/07/201901 October 2019
16336930/07/201914 October 2019
14123530/07/201915 October 2019
15004103/07/201922 October 2019
15075523/07/201917 December 2019

 

I would like to show a cohort table using the above data 

How many students applied in a specific month and out of which how many deposits happened in subsequent months.

with below approach, it is giving me the number of applications sent by students in a specific month but it's not giving me number of deposits on the same month

Example:

In the month of July 2019, 96 applications out of which 3 deposits made on July 2019, 8 deps made on Aug2019, 5 in Sep2019, 4 in Oct2019, and 1 in December 2019.

My cohort table is showing deposits for all the months except July.

Can someone please help me with the same?

 

Below is my approach

New customers count =

var acquisitionMonth = SELECTEDVALUE(UnionSelectColumns[Rankx])
var selectedFutureMonth = SELECTEDVALUE('Cohort Months'[No])

var currentCustomers = DISTINCT(VALUES(UnionSelectColumns[Emails]))
var pastCustomers = CALCULATETABLE(DISTINCT(VALUES(UnionSelectColumns[Emails])),ALL(UnionSelectColumns[M&Y],UnionSelectColumns[M&Y No]),
UnionSelectColumns[Rankx]<acquisitionMonth)
var newCustomers = EXCEPT(currentCustomers,pastCustomers)
var customersFuturePeriods = CALCULATETABLE(VALUES(UnionSelectColumns[Emails]),ALL(UnionSelectColumns[M&Y],UnionSelectColumns[M&Y No]),UnionSelectColumns[Rankx] = acquisitionMonth + selectedFutureMonth)
var newCustomersBuyingInFuturePeriods = INTERSECT(newCustomers,customersFuturePeriods)
RETURN COUNTROWS(newCustomersBuyingInFuturePeriods)
 
 

 

 

General Comment
Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Bharani308 ,

     

    Maybe you can modify the var:

    pastCustomers =
    CALCULATETABLE (
        DISTINCT (  UnionSelectColumns[Emails]  ),
        ALL ( UnionSelectColumns[M&Y], UnionSelectColumns[M&Y No] ),
        UnionSelectColumns[Rankx] <= acquisitionMonth
    )

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.