Historical New Customer Measure

[findingsolution]

Hi there,

I currently have a measure to calculate a 'new customer' (new unique emails seen in the last 6 months) in my sales data. However, I can't work out how to modify this measure so that I can see how many 'new customers' there were in Jan, Feb etc (a point in time view). I want to see the growth or decline of this customer segment over time (preferably in a bar chart). Can someone help with my measure?

New Customers = 
VAR currentCustomers = VALUES('Sales'[email])
VAR currentdate = EDATE(today(),-6)
VAR pastCustomers = CALCULATETABLE(VALUES('Sales'[email],ALL('Sales'[payment_date].[Month],'Sales'[payment_date].[MonthNo],'Sales'[payment_date].[Year]),'Sales'[payment_date]<currentdate)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

I have tried changing VAR currentdate = EDATE(today(),-6) to VAR currentdate = EDATE("31/01/2022",-6) to get new customers from January, but I think it is also including all the people that are new after January as well, so I cannot get a point in time view of my measure. 

Thank you!

[v-cgao-msft]

Hi @findingsolution ,

//I have tried changing VAR currentdate = EDATE(today(),-6) to VAR currentdate = EDATE("31/01/2022",-6) to get new customers from January

Try replacing TODAY() in the formula with MAX('Sales'[payment_date]) .

If this doesn't work for you, please consider sharing more details.

similar question.

Solved: Get the list of User form the previous month not i..

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Ashish_Mathur]

Hi,

Share the download link of your PBI file and show the expected result.

[findingsolution]

I can't share the file because the data is sensitive, but this is what the current measure's output is:

But you can see when I try and fix the date (in the second half of my message), this happens:

Happy to try any other types of measures that would get me to the same result.

[Ashish_Mathur]

Hi,

Not only will i need a file to work with, I will also need to know the expected result.