Rolling L12 Months Calculated Column

[mk5235]

Hello,

I have data structured in the columns below and also a calendar table:

Customer ID

EOM Date

Monthly Sales

I want to add L12 months sales and PY L12 months sales calculcated columns so I can create a rolling churn flag (if L12 is 75% or less of PY L12 then 1 else 0) then count the number of churned customers for any period. 

Can someone help with the most efficient formula to create the calculated columns? 

I keep seeing people say not to use calculated columns for this but not sure how I can get the count result without them so any help there would be appreciated as well.

Thanks!

[v-jingzhang]

Hi @mk5235 

What does "L12 months" mean? Since you have monthly sales data in the table, do you want to calculate the total sales of the past 12 months for every month row? For example, if a row has EOM Date 2022/9/30, you want to calculate the total sales with EOM dates from 2021/10/31 to 2022/9/30 for the same customer as "L12 months sales"? And then calculate totals sales from 2020/10/31 to 2021/9/30 as "PY 12 months sales"? Do I understand the requirement correctly?

Best Regards,
Community Support Team _ Jing

[mk5235]

Hi - yes, thats exactly what I'm looking for!

[v-jingzhang · Accepted solution]

Hi @mk5235 

You can create two calculated columns with below code. 

L12 Month Sales = SUMX(FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID]) && 'Table'[EOM Date]<=EARLIER('Table'[EOM Date]) && 'Table'[EOM Date]>EDATE(EARLIER('Table'[EOM Date]),-12)),'Table'[Monthly Sales])

PY L12 Month Sales = SUMX(FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID]) && 'Table'[EOM Date]<=EDATE(EARLIER('Table'[EOM Date]),-12) && 'Table'[EOM Date]>EDATE(EARLIER('Table'[EOM Date]),-24)),'Table'[Monthly Sales])

If you want to count the number of churned customers for any month period, your idea with creating calculated columns is doable because your sales data is already in Month granularity. If the "Period" needs to be flexible e.g. count the number of churned customers for any month/quarter/year, using measures will be more flexible but the calculation speed may not be improved. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.