I have a table that contains: Client, Start Date, Stop Date for a subscription. Clients can have multiple subscriptions at the same time (starting and ending on different dates). Clients can also stop all subscriptions then restart them again months or years later.
The current measure that I am using counts ALL the starts - even if the client is not starting (because they already have another subscription).
StartCount =
// using this measure for a visual with months on x axis so min date would be start of month, max date would be end of month //calendar table is not connected to Table1
Var MyMindate = Min(Calendar[Date])
Var MyMaxdate = Max(Calendar[Date])
Return
Calculate(DistinctCount(Table1[Client]),Table1[Start]>=MyMindate && Table1[Start]<MyMaxdate)
I want to write a measure in dax that will count when a client starts or stops ALL subscriptions with us so that I can display monthly starts and stops. The problem is that if I simply count start dates, I get an inaccurate number because it is counting the start of EACH subscription rather than the start of the client. and if I simply count the MINIMUM start date for each client, it ignores all those cases where a client cancelled all subscriptions and left us then returned months or years later with a new start date.
Below is a table with a notes column to explain what SHOULD be counted vs not counted
Any suggestions as to how to write a dax measure for this? Thanks
| Client | Start | Stop | Note about counting starts |
| a | 2021-10-13 | 2024-11-02 | 1 - start |
| a | 2023-04-14 | 2024-11-02 | 0 - not a start because already has an active subscription |
| a | 2024-12-15 | 2026-03-01 | 1 - start (because previous subscription ended a month earlier) |
| b | 2021-01-01 | 2022-12-01 | 1 - start |
| b | 2023-12-01 | 2027-02-16 | 1 - start because previous subscription ended a year earlier |
| c | 2022-01-19 | 2022-10-17 | start |
