Forum Discussion
Help building subscription status table
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.
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") )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.
