Slicer issue between related date tables

[JWT]

Hi

I am trying to measure number of new customers in a period. For this I want to use a custom period date slicer.

In my dataset I have to connected date tables: the date table (dim_date) and a related copy of the table (special_date), that includes a range of period filters (12 mths, YTD, MTD eg.). The two tables is related (both way relationship).

I am using this measure for new customers:

New Customers = 
VAR currentCustomers = VALUES(fact_sales_invoiced[email])
VAR currentDate = MIN(dim_date[date])
VAR pastCustomers = CALCULATETABLE(VALUES(fact_sales_invoiced[email]),ALL(dim_date[date]),dim_date[date]<currentDate)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN 
COUNTROWS(newCustomers)

The measure works if I use the dim_date table to slice by, but not the special_date table. It seems that the problem is the calculations of "pastCustomers" that doesn't work when I slice by "special date" periods. The "special date" slicer works for everything else!

Any ideas why this doesn't work?

Thanks in advance!

[lbendlin]

In my dataset I have to connected date tables: the date table (dim_date) and a related copy of the table (special_date), that includes a range of period filters (12 mths, YTD, MTD eg.). The two tables is related (both way relationship).

What prevents you from merging these tables?

[JWT]

Yes, maybe that is the best way to go about it. I guess it was just my preference to keep the data table "clean" from additions. Still bothers me that it does not work! 😊