The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
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?
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! 😊