Tracking Cumulative Subscriptions over Time

[JacktheYeti]

Hi there!

I am trying to create a report that visualizes a platform's subscription data over time.

In the database there are 2 different timestamps created when a new subscription occurs:

1)  the 'date_created' field which represents the date the subscription was created and

2) the 'date_expiration' field which represents the date when the subscription expires.

I'm looking to show cumulative subscribers each month that takes into account the subscribers gained(from date_created, and the subscribers lost (from date_expired), but I'm not quite sure how to attack this problem.

Thanks for any help \m/

[JacktheYeti]

@v-cherch-msft  This is great, thanks for much for your help.

The numbers look correct with regards to the # of subscribers who started their subscription that month but it doesn't seem to aggregate the numbers from the previous months.

In other words, if I had 8 subscriptions in January, and an additional 8 subscriptions in February, but 4 of them expire in February, my net subscriptions should be 12.

[v-cherch-msft · Accepted solution]

Hi @JacktheYeti 

You may modify the formula like below:

Measure =
CALCULATE (
    COUNT ( 'Table'[subscriber] ),
    FILTER (
        GENERATE ( 'calendar', 'Table' ),
        'Table'[date_created] <= 'calendar'[Date]
            && DATE ( YEAR ( 'Table'[date_expiration] ), MONTH ( 'Table'[date_expiration] ), 1 ) > 'calendar'[Date]
    )
)

Regards,

[v-cherch-msft]

Hi @JacktheYeti 

You may create a calendar table and use it.Then create a measure like below:

Measure =
CALCULATE (
    COUNT ( 'Table'[subscriber] ),
    FILTER (
        GENERATE ( 'calendar', 'Table' ),
        'Table'[date_created] <= 'calendar'[Date]
            && 'Table'[date_expiration] >= 'calendar'[Date]
    )
)

Regards,