so I have a sheet with toggle built for current month, prior month and month before last, and year to date
with that I have a table to show customer, sales manager, customer location, city and sales
so when user hit the toggle for current month, the table shows all those new customers with the sales of June and other related dimension info, same thing , if users click prior month, the table toggle into new customers with June to date sales.
At the table's sales field, I have set expression but not so that efficiency, quite length.
vDashboard connects four different time frame, so the first one below is to get new customers in July ( no sales prior to July)
pick(vDashboard,
if(monthstart(min({$<[Unit Sales]={'>0'}, SaleYear=,SaleQuarterYear=, SaleMonthYear=,
Saledate={'<=$(=max(Saledate))'}>} Saledate))=MonthStart((vSaleMaxDate)),Sum({<SaleMonthYear={'$(vCM)'}>}[Unit Sales]),0) #new customers in July
,
if(monthstart(min({$<[Contract Sales]={'>0'}, SaleYear=,SaleQuarterYear=, SaleMonthYear=, Saledate={'<=$(=max(Saledate))'}>} Saledate))=MonthStart(date(floor(monthstart(addmonths((vSaleMaxDate),-1))), 'YYYYMMDD')),Sum({<SaleMonthYear={'$(vCM-1)'}>}[Contract Sales]),0) # #new customers in June
,
two questions
1. how do I do YTD new customers? which means the customers only have sales since Jan 24.
2. is there a better way to optimize this lengthy set expression？
Thanks
