For the first question, to count customers I am counting the unique number of [Customer Nos].
count( distinct [Customer No])
What i noticed at the bottom of the screenshot, there are 6 records but the customer no is the same in all the records... is that one customer or six ? If it is six, please tell me how to uniquely identify a customer ? If its the combination of customer no and contract for example you would adjust the expression as follows:
count( distinct [Customer No] & [Contract No] )
For the 2nd question, there are 3 date dimensions. Cust Start Date , Cust End Date and YearMonth (which is actually a date and has Year and Month as separate fields)
When you select Month, it is going to filter for all customers that are ACTIVE... any customer that has cust start the same or less than the selected month and any customer that has a cust end same or after the selected month. So you will see cust start dates that are OUTSIDE the selected month. That is how interval matching works.
If you want to select a month and show only the customers that started in that month, you need to add a new field in the load that calculates the month (and perhaps year) off the cust start date .
For example, augment this:
Date([Cust Start Date]) as [Cust Start Date],
Date([Cust End Date]) as [Cust End Date],
to:
Date([Cust Start Date]) as [Cust Start Date],
Year(Date([Cust Start Date])) as [Cust Start Year],
Month(Date([Cust Start Date])) as [Cust Start Month],
Date([Cust End Date]) as [Cust End Date],
Year(Date([Cust End Date])) as [Cust End Year],
Month(Date([Cust End Date])) as [Cust End Month],
Then in the UI, filter on [Cust Start Month] and [Cust Start Year] .... it will associate how you want.
