Forum Discussion
Help building subscription status table
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-numbers/
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.
4 Replies
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] )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?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") )
