Churn of customers acquired in previous period

[MrBean]

Hi guys and gals,

Working for a subscription service I am trying to figure out the number and value of susbcriptions and customers who ended their subscription after pre-defined periods (e.g. 1 month, 2 months etc.). For this I do have a Subscription table with subscription details (subscription ID, customer ID, start date, end date, payment frequency, year value), a Transaction table with all detailed transactions (subscription ID, customer ID, transaction ID, transaction date, transaction amount) and a standard Calendar table. In the model, the date of this date table is connected to the start date in the Subscription table and the transaction date in the Transaction table.

The DAX measures I wrote are like:

ChurnSubscriptionLT030Days# =

CALCULATE(

    [TotalSubscriptions],

    DATESINPERIOD(Subscription[startdate], MAX(Subscription[startdate]),-30,DAY),

    DATESINPERIOD(Subscription[enddate], MAX(Subscription[startdate]),-30,DAY)

)

When viualising these measures in a bar graph with the measure on Y-axis and the calendar month (MONTH('Calendar'[Date]) on X-axis the numbers are incorrect. I am sure the solution is simple so I am hoping you can help me out.

[amitchandak]

@MrBean , Create current vs last period as I have done in the HR file(On the trend Page). Then you can check blank and nonblank values for customer retention

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...