I have sales and calendar table in my model. I am trying to do new customer count based on date selected. The definition of new customer is that customer must not have appeared in database til that reporting period. For example, if user selected Dec 2015, they only want to see customer who had sales only in Dec 2015. Obviously calculated column doesn’t help here as the new customer count is changing based on period selected. Also, the user wants to see this measure further broken down by other attributes such as city, age, etc.
I wrote a measure as below:
IF(CALCULATE(MIN('Sales'[Sales Date]),ALL('Dates'))>=MIN('Dates'[Date])&&CALCULATE(MIN('Sales'[Booking Date]),ALL('Dates'))<=MAX('Dates'[Date]),1,0)
What it does is give me the earliest sales date for each customer, if that date is between the min and max of selected date, count that as new customer. However this measure only works when you have customer key in the view. If I want to show number of new customers in each city for Dec 2015, my measure doesn’t work.
Any idea what’s the solution to this problem?
