Dax or Var formula for multiple if statements for Churn

[Anonymous]

Hi All,

I am loolking to create an IF statement for customer churn based on 3,6,9 months. Examaple below:

IF, 3 months with 0 Rev, then"At Risk 1"

IF, 6 months with 0 Rev, then"At Risk 2"

IF, 9 months with 0 Rev, then"At Risk 3"

IF, OVER 9 months with 0 Rev, then"Churned Customer"

[bhanu_gautam]

@Anonymous , Please try below formula to calulate revenue and categorize it

CustomerChurnStatus =
VAR CustomerID = 'YourTableName'[CustomerID] -- Replace 'YourTableName' with the actual name of your table
VAR CurrentDate = TODAY()

-- Calculate total revenue for the last 3, 6, and 9 months for the customer
VAR RevenueLast3Months =
CALCULATE(SUM('YourTableName'[Revenue]), FILTER('YourTableName', 'YourTableName'[CustomerID] = CustomerID && 'YourTableName'[Date] >= CurrentDate - 90 && 'YourTableName'[Date] < CurrentDate))

VAR RevenueLast6Months =
CALCULATE(SUM('YourTableName'[Revenue]), FILTER('YourTableName', 'YourTableName'[CustomerID] = CustomerID && 'YourTableName'[Date] >= CurrentDate - 180 && 'YourTableName'[Date] < CurrentDate))

VAR RevenueLast9Months =
CALCULATE(SUM('YourTableName'[Revenue]), FILTER('YourTableName', 'YourTableName'[CustomerID] = CustomerID && 'YourTableName'[Date] >= CurrentDate - 270 && 'YourTableName'[Date] < CurrentDate))

RETURN
IF (
RevenueLast3Months = 0,
"At Risk 1",
IF (
RevenueLast6Months = 0,
"At Risk 2",
IF (
RevenueLast9Months = 0,
"At Risk 3",
IF (
RevenueLast9Months > 0,
"Active Customer",
"Churned Customer"
)
)
)
)

Accept as solution and give kudos if it helps