none
Need to CALCULATE Subscription Retention with Start and End Dates

    Question

  • Hi, I have data for subscriptions purchased per week and am trying to show the retention as weeks progress. The data looks like this:
    SubID                 SubBeginWeek            SubEndWeek
    1000ADW           12/30/2012                 2/13/2013
    2599BRG            1/20/2013                   2/24/2013
    3800ADC            2/17/2013                   3/17/2013
    9809IUO            12/30/2012                  2/10/2013

    etc. I have created a simple PowerPivot table with the rows being SubBeginWeek and the columns SubEndWeek and with COUNTA(SubID) as the value. This gives me a PowerPivot that looks like this:

                         12/30/2012    1//6/2012     1/13/2012     1/20/2012     1/27/2012    etc.
    12/30/2012              2053           1727               996               897             1998 
    1/6/2012                                    1536               789               954             1024 
    1/13/2012                                                       1345               768               887 
    1/20/2012                                                                             879             1879 
    1/27/2012                                                                                                1888 
    etc.      

    which is telling me how many subscribers CANCELLED in each week (i.e., for those who signed up in the week of 12/30/2012, 2053 cancelled the first week, 1727 cancelled the second week, etc.). However, what I want to show is how many REMAINED in each week.

    Is it possible to create a measure with CALCULATE with ALL that will give me the values I want (i.e., all those who signed up in the week minus those who cancelled)?

    Any help greatly appreciated!

    Kathryn


    Kathryn Birstein, Senior SharePoint Architect

    Tuesday, October 15, 2013 10:57 AM

Answers

  • Hi Kathryn,

    I'm not sure if I've 100% understood you're requirement but perhaps showing you how I've approached this problem will help.

    Pleae note that the the dates are in dd/mm/yyyy format.

    Firstly, this is the data that I've used...

    SubID SubBeginWeek SubEndWeek
    1 06/01/2012 27/01/2012
    2 13/01/2012 13/01/2012
    3 20/01/2012 27/01/2012
    4 27/01/2012 27/01/2012
    5 06/01/2012 20/01/2012
    6 13/01/2012 20/01/2012
    7 20/01/2012 27/01/2012
    8 27/01/2012 27/01/2012
    9 06/01/2012 20/01/2012
    10 13/01/2012 13/01/2012
    11 20/01/2012 20/01/2012
    12 27/01/2012 03/02/2012
    13 06/01/2012 27/01/2012
    14 13/01/2012 27/01/2012
    15 20/01/2012 27/01/2012
    16 27/01/2012 03/02/2012
    17 06/01/2012 27/01/2012
    18 13/01/2012 20/01/2012
    19 20/01/2012 27/01/2012
    20 27/01/2012 03/02/2012
    21 27/01/2012 03/02/2012
    22 27/01/2012 03/02/2012
    23 27/01/2012 03/02/2012
    24 27/01/2012 10/02/2012
    25 27/01/2012 10/02/2012
    26 27/01/2012 10/02/2012
    27 27/01/2012 10/02/2012

    ..and this is the outcome (filtered by Januray i.e. MonthOfYearNumber = 1):

    You can see above that for each week, the numbers for new subscriptions, ended subcriptions, and the remaining subscriptions are shown.

    1. I've introduced a 'Date' table into the model which has the dates for all years in 2012.
    2. I have defined two relationships between this 'Date' table and the 'Subscription' table. An 'active' relationship is defined on the Subscription[SubBeginWeek] column and the 'Date'[Date] column, where the 'Date' table is the lookup. An 'inactive' relationship has been defined between the Subscription[SubEndWeek] column and the 'Date'[Date], where the 'Date' table is the lookup.
    3. I have defined the following calculations on the 'Subscription' table:

    CountOfSubscriptions:=CALCULATE(COUNT(Subscription[SubID]))
    CountOfSubEnd:=CALCULATE([CountOfSubscriptions], USERELATIONSHIP(Subscription[SubEndWeek], 'Date'[Date]))
    RemainingSubscriptions:=[CountOfSubscriptions] - [CountOfSubEnd]

    The first calculation is the same as the one you mentioned in your post and will be evaluated within the context of the 'SubBeginWeek' date since this column has the active relationship with the 'Date' table.

    The second calculation is evaluating the [CountOfSubscriptions] measure within the context of the inactive relationship between the 'Subscription' table and the 'Date' table i.e. 'SubEndWeek', thus giving us the number of ended subscriptions in the selected/filtered period.

    The third calculation is simply calculating the difference between the two.

    Does this help at all?

    Michael

    Monday, October 21, 2013 10:34 AM
    Answerer

All replies

  • Hi Kathryn,

    I'm not sure if I've 100% understood you're requirement but perhaps showing you how I've approached this problem will help.

    Pleae note that the the dates are in dd/mm/yyyy format.

    Firstly, this is the data that I've used...

    SubID SubBeginWeek SubEndWeek
    1 06/01/2012 27/01/2012
    2 13/01/2012 13/01/2012
    3 20/01/2012 27/01/2012
    4 27/01/2012 27/01/2012
    5 06/01/2012 20/01/2012
    6 13/01/2012 20/01/2012
    7 20/01/2012 27/01/2012
    8 27/01/2012 27/01/2012
    9 06/01/2012 20/01/2012
    10 13/01/2012 13/01/2012
    11 20/01/2012 20/01/2012
    12 27/01/2012 03/02/2012
    13 06/01/2012 27/01/2012
    14 13/01/2012 27/01/2012
    15 20/01/2012 27/01/2012
    16 27/01/2012 03/02/2012
    17 06/01/2012 27/01/2012
    18 13/01/2012 20/01/2012
    19 20/01/2012 27/01/2012
    20 27/01/2012 03/02/2012
    21 27/01/2012 03/02/2012
    22 27/01/2012 03/02/2012
    23 27/01/2012 03/02/2012
    24 27/01/2012 10/02/2012
    25 27/01/2012 10/02/2012
    26 27/01/2012 10/02/2012
    27 27/01/2012 10/02/2012

    ..and this is the outcome (filtered by Januray i.e. MonthOfYearNumber = 1):

    You can see above that for each week, the numbers for new subscriptions, ended subcriptions, and the remaining subscriptions are shown.

    1. I've introduced a 'Date' table into the model which has the dates for all years in 2012.
    2. I have defined two relationships between this 'Date' table and the 'Subscription' table. An 'active' relationship is defined on the Subscription[SubBeginWeek] column and the 'Date'[Date] column, where the 'Date' table is the lookup. An 'inactive' relationship has been defined between the Subscription[SubEndWeek] column and the 'Date'[Date], where the 'Date' table is the lookup.
    3. I have defined the following calculations on the 'Subscription' table:

    CountOfSubscriptions:=CALCULATE(COUNT(Subscription[SubID]))
    CountOfSubEnd:=CALCULATE([CountOfSubscriptions], USERELATIONSHIP(Subscription[SubEndWeek], 'Date'[Date]))
    RemainingSubscriptions:=[CountOfSubscriptions] - [CountOfSubEnd]

    The first calculation is the same as the one you mentioned in your post and will be evaluated within the context of the 'SubBeginWeek' date since this column has the active relationship with the 'Date' table.

    The second calculation is evaluating the [CountOfSubscriptions] measure within the context of the inactive relationship between the 'Subscription' table and the 'Date' table i.e. 'SubEndWeek', thus giving us the number of ended subscriptions in the selected/filtered period.

    The third calculation is simply calculating the difference between the two.

    Does this help at all?

    Michael

    Monday, October 21, 2013 10:34 AM
    Answerer
  • Hi Michael,

    WOW, this looks great! Unfortunately I'm only on PowerPivot 1.0 but I'm trying to make it work. Will let you know!

    Thanks a million,

    Kathryn


    Kathryn Birstein, Senior SharePoint Architect

    Wednesday, October 23, 2013 2:40 PM
  • Hello Kathryn:

    I used Michael's test data but took a different approach.

     

    One thing about Power Pivot and DAX....there are many ways to approach a problem.....

    John


    John Lacher john@pivotdashbooard.com

    • Proposed as answer by John Lacher Friday, October 25, 2013 3:29 PM
    Thursday, October 24, 2013 1:59 PM
  • Hi John,

    Your display is the way the client wants to see it! And your code is amazingly clever! I'm trying it out today. . .


    Kathryn Birstein, Senior SharePoint Architect

    Friday, October 25, 2013 5:41 PM