Help building subscription status table

[Anonymous]

Hi,

I'm in need of assitance in regards to building a complete status list on a day to day basis for our subscription model.

The dataset contains a single line for each single membership, which provides me with a CustomerKey, FirstPayment and LastPayment date, and the current membership status - all of which are updated daily.

Example:

SUBSCRIPTIONID	FIRSTPAYMENT	LASTPAYMENT	SUBSCRIPTIONSTATUS
4056	27-10-2016 00:00	28-02-2017 00:00	Actice
4058	27-10-2016 00:00	27-10-2016 00:00	Suspended

To evaluate the subscription status I want to use the following expresion, which then needs to be calculated for every single day in our date table.

STATUS =
If([FIRSTPAYMENT]>DATE;"";
    -- Subscription hasn't occured yet
    if(EDATE([LASTPAYMENT];1)>DATE;"ACTIVE";
        --The subscription expires 1 month after the customers lastpayment
        [SUBSCRIPTIONSTATUS]
    )

So far I've concluded that probably need to use calculated table or summarizedcolomns, based on this link, but then I got stuck:
https://pbidax.wordpress.com/2015/09/27/use-calculated-table-to-figure-out-monthly-subscriber-number...

Anyone up for some advice?

Additionally, I'm aware that the output table would be rather large, as it would be around 5,9 million lines with our relatively small customer base of 4.000 users - unless it can be constructed in a measure -, so alternatively I was thinking the dataset could be limited to weekly or monthly.

[Phil_Seamark]

Is this caluclated table along the lines of what you need?

New Table = SELECTCOLUMNS(
        FILTER(
             CROSSJOIN('Table1',CALENDARAUTO()),
             'Table1'[FIRSTPAYMENT] <= [Date] && 'Table1'[LASTPAYMENT] >= [Date]
             ),
             "SubscriptionID",[SUBSCRIPTIONID],
             "Subscription Status" , [SUBSCRIPTIONSTATUS],
             "Date" , [Date]
             )

[Anonymous]

Hi, it's definitely along the lines, but unfortulatly not 100%.

For each member, the table only seem to calculate the status for a duration of 1 month.

I'm quessing it's due to the FILTER( CROSSJOIN( ) )

As an example, I know one of our oldest members was registered on 28-06-2016, but the last line generated for that member is 28-07-2016, were that member is still active today.

 I made a "manual" calculation in Excel for 28-02-2017, which returned 2.302 active users, so we're a bit off.

Also, it misses the element of:

if(EDATE([LASTPAYMENT];1)>DATE;"ACTIVE";

Such that a user is registered as active for 1 month after they made their lastpayment.

i.e. if a subscriber cancels their subscription in the middle of the month, they will still have the status of "Active" until their subscription expires. Were the [SUBSCRIPTIONSTATUS] will change from the data source, as soon as the subscriber changes their subscription.

Example:

FIRSTPAYMENT	LASTPAYMENT	CANCELDATE	EXPIRATIONDATE	SUBSCRIPTIONSTATUS
01-11-2016	01-02-2017	03-02-2017	01-03-2017	CANCELLED
				
USERID	Date	STATUS		
1054	01-11-2016	ACTIVE		
1054	02-11-2016	ACTIVE		
1054	03-11-2016	ACTIVE		
	-----			
1054	27-02-2017	ACTIVE		
1054	28-02-2017	ACTIVE		
1054	01-03-2017	ACTIVE		
1054	02-03-2017	CANCELLED		

The reason for this is that a subscriber can still utilize their subscription until expiration.

Do you know how to incorporate those conditions?

Edit:
What if I remove the filter, which seems to return all the lines I need, but the "wrong" Subscription Status, and then Calculate the subscription status for each line, based on a lookup in my table 1, and the Firstpayment / Lastpaymenter interval?

Or can this be completed in one go instead?

[Eric_Zhang]

@Anonymous

The explaination doesn't make things much clear for me. Check if this DAX help.

New Table  = ADDCOLUMNS(FILTER(
             CROSSJOIN('Table1',CALENDARAUTO()) ,[Date]>=Table1[FIRSTPAYMENT]&&[Date]<= DATE(YEAR(Table1[LASTPAYMENT]),MONTH(Table1[LASTPAYMENT])+1,DAY(Table1[LASTPAYMENT]))),
             "SUBSCRIPTIONSTATUS_",IF(Table1[LASTPAYMENT]=[Date],Table1[SUBSCRIPTIONSTATUS],"active")                         
             )

[Anonymous · Accepted solution]

I fixed my problem by using the query editor instead.

As an Excle user, I'm almost more at home in the query editor than in the viewer.