New, Retuning and Churn Customer Count based on Current and Previous Fiscal Years

[abhiram342]

Hi Team,

I have FactSales table which the Customer,ID SalesAmount and Date fields. I want to create DAX measure for below scenario. Can someone please assist?

Fact Table : Date,CustomerID,SalesAmount

Customer Table: CustomerID, CustomerName

Date Table: Date,Month,FiscalYear,Fiscal Quater

DAX Metrics

1. New Customers

Customer which are present in current fiscal year (FY22) and doesn't present in previous Fiscal Year (FY21)

2. Returning Customers

Customers which are present in Previous Fiscal Year ( FY21) and present in current Fiscal Year as well (FY22)

3.Churn

Customers which are present in present in previous Fiscal Year (FY21) and doesn't present in current fiscal years(FY22)

Thanks,

Abhiram

[VahidDM]

Hi @abhiram342 

You need to use EXCEPT and INTERSECT for those measures:

1. New Customers : use filter to find list of FY22 and FY21 then use Except(FY22,FY21)

2. Returning Customers: Intersect(FY21,FY22)

3.Churn: Except(FY21,FY22)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!