Forum Discussion

Rohit17's avatar
Rohit17
Frequent Visitor
2 years ago

Active subscriptions and New active subscriptions Dax measures

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 ...
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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())
)
)
Rohit17's avatar
Rohit17
Frequent Visitor
2 years ago

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

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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).