Forum Discussion

JacktheYeti's avatar
JacktheYeti
Helper I
7 years ago
Solved

Tracking Cumulative Subscriptions over Time

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)...
Need Help
Tutorial Requests
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

JacktheYeti's avatar
JacktheYeti
Helper I
7 years ago

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's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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,