Forum Discussion
Dax measure to count client STARTS when I client has multiple records
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 |
Easy enough,
Hi,
Based on the table that you have shared, show the expact result which you are expecting.
10 Replies
- lbendlin
Super User
Does it have to be DAX? This seems like immutable data, so could also be done in Power Query?
- Kerry_M
Helper II
Actually I would love to do it in Power Query if I can but I don't know how. The table has about 600,000 records so takes a long time when I try out ideas.
- ThxAlot
Super User
- Ashish_Mathur
Super User
Hi,
Based on the table that you have shared, show the expact result which you are expecting.
- Kerry_M
Helper II
The expected result would be 5
- Ashish_Mathur
Super User