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

 

5 Replies

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

  • Rohit17's avatar
    Rohit17
    Frequent Visitor
    2 years ago

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