Dynamically change how to define "New Customers" vs "Existing Customers" based on slicer

[ruesaint_denis]

Hi everyone, 

I have a complicated question that I don't know how to begin to address. Every month, and every quarter, my boss would like a comparison on a variety of behaviors of "New Customers", those that became customers that particular month or quarter, compared to "Existing Customers", those that were customers prior to the time period in question. 

I've been creating a new calculated column each time period like "April New Customer?" labelling it New or Existing, but this isn't very sustainable. I would like to have this be dynamic, where I can have a slicer and the slicer determines which month qualifies as "New" and whatever came prior is "Existing". 

An example of a task is calculating Sales showing difference between New vs. Existing. 

I have a date table, and a user table. the User table has the column "First Completed Order Completed At" which signifies when someone became a customer. 

Would anyone have any hints of how I can begin to address this? I can't share a sample of my data.

Thank you, 

Denisse

[Whitewater100]

Hi:

You can try this but changing field names to match yours. The Customer ID should come from your fact or transaction table. [Churn Time Value] is a parameter and I'll put that below. This distinquishes how to define new or lost.

New Customers = //calculating which customers within any particular month have purchased but haven't done so for the last x number of days

VAR CustomerTM = VALUES( Sales[Customer ID] )

VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ),

FILTER( ALL( Dates ),

Dates[Date] > MIN( Dates[Date] ) - [Churn Time Frame Value] &&

Dates[Date] < MIN( Dates[Date] ) ) )

RETURN

COUNTROWS(

EXCEPT( CustomerTM, PriorCustomers ) )

Measure for churn time:

Churn Time Frame Value = SELECTEDVALUE('Churn Time Frame'[Churn Time Frame], 90)

New Customer Sales =

VAR CustomerTM = VALUES( Sales[Customer ID] )

VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ),

FILTER( ALL( Dates[Date] ),

Dates[Date] > MIN( Dates[Date] ) - [Churn Time Frame Value] &&

Dates[Date] <= MIN( Dates[Date] ) ) )

RETURN

CALCULATE( [Total Sales],

EXCEPT( CustomerTM, PriorCustomers ) )

[ruesaint_denis]

Hi @Whitewater100 , I'm looking more for a dynamic labelling, where depending on the selected month or quarter, it decides what's New v. Existing: 

Here I have my Total Sales measure, and I add a legend where it separates the sales by New v. Existing