topic Re: Active subscriptions and New active subscriptions Dax measures in DAX Commands and Tips

Active subscriptions and New active subscriptions Dax measures

Rohit17 — Thu, 08 Aug 2024 11:12:02 GMT

Hello everyone,
Based on the columns given below
I need to calculate total Active subscriptions , total New active subscriptions, total new usubscribed subscriptions
Can anyone help me ?

Active subsctions is easy to calculate but I am not able to create measure for New active subscriptions.
Need to show them in line chart

Total new subs = subs created on that months
total new unscibeds = those who canceled

Could you please assist on this

SUBSCRIPTION_ID
TIER

PRODUCTS_QTY

SUBSCRIPTION_STATUS

SUBSCRIPTION_EMAIL

SUBSCRIPTION_PAYMENT_METHOD

SUBSCRIPTION_INTERVAL

SUBSCRIPTION_START_DATE

CANCELATION_DATE

PAUSED_DATE

ACTIVE_FROM_PAUSED_DATE

I want somehhat lik this

Re: Active subscriptions and New active subscriptions Dax measures

bhanu_gautam — Thu, 08 Aug 2024 11:26:36 GMT

Rohit17 , You can create a new measure

Total Active Subscriptions =

CALCULATE(

COUNTROWS(Subscriptions),

Subscriptions[SUBSCRIPTION_STATUS] = "Active"

)

And one more Total Active Subscription in a month

Total New Active Subscriptions =
CALCULATE(
COUNTROWS(Subscriptions),
FILTER(
Subscriptions,
Subscriptions[SUBSCRIPTION_START_DATE] >= STARTOFMONTH(TODAY()) &&
Subscriptions[SUBSCRIPTION_START_DATE] < ENDOFMONTH(TODAY())
)
)

This measure will count the number of subscriptions that were canceled in the current month.

Total New Unsubscribed Subscriptions =
CALCULATE(
COUNTROWS(Subscriptions),
FILTER(
Subscriptions,
Subscriptions[CANCELATION_DATE] >= STARTOFMONTH(TODAY()) &&
Subscriptions[CANCELATION_DATE] < ENDOFMONTH(TODAY())
)
)

Re: Active subscriptions and New active subscriptions Dax measures

Rohit17 — Thu, 08 Aug 2024 11:40:25 GMT

Hi bhanu_gautam ,

Thanks for checking this

But this is referring to current date. What about earlier month ?
for example I wanted to check new active subs in January and new unsubscribed subs .
This won't work right ? let me know if I am thninking right or wrong

Re: Active subscriptions and New active subscriptions Dax measures

bhanu_gautam — Thu, 08 Aug 2024 11:47:39 GMT

Right, first measure will remain as it is

update second one as

dax
TotalNewActiveSubscriptions =
CALCULATE(
COUNTROWS(Subscriptions),
FILTER(
Subscriptions,
Subscriptions[SUBSCRIPTION_START_DATE] >= STARTOFMONTH(DateTable[Date]) &&
Subscriptions[SUBSCRIPTION_START_DATE] < ENDOFMONTH(DateTable[Date])
)
)

And 3rd one as

TotalNewUnsubscribedSubscriptions =
CALCULATE(
COUNTROWS(Subscriptions),
FILTER(
Subscriptions,
Subscriptions[CANCELATION_DATE] >= STARTOFMONTH(DateTable[Date]) &&
Subscriptions[CANCELATION_DATE] < ENDOFMONTH(DateTable[Date])
)
)

And make sure you have date table

Ensure that your Subscriptions table is related to the DateTable on the appropriate date fields (e.g., SUBSCRIPTION_START_DATE and CANCELATION_DATE).

Re: Active subscriptions and New active subscriptions Dax measures

Rohit17 — Mon, 12 Aug 2024 11:42:35 GMT

hi bhanu_gautam , The graph is not appearing correctly when added all three measures

Re: Active subscriptions and New active subscriptions Dax measures

Rohit17 — Mon, 12 Aug 2024 11:48:02 GMT

hi bhanu_gautam - created these measures and added to chart however this is not givving me the result like i attached in the problem