Forum Discussion

Kerry_M's avatar
Kerry_M
Icon for Helper II rankHelper II
1 year ago
Solved

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

 

 

ClientStartStopNote about counting starts
a2021-10-132024-11-021 - start
a2023-04-142024-11-020 - not a start because already has an active subscription
a2024-12-152026-03-011 - start (because previous subscription ended a month earlier)
b2021-01-012022-12-011 - start
b2023-12-012027-02-161 - start because previous subscription ended a year earlier
c2022-01-192022-10-17start

10 Replies

  • Does it have to be DAX?  This seems like immutable data, so could also be done in Power Query?

    • Kerry_M's avatar
      Kerry_M
      Icon for Helper II rankHelper 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.

    • Kerry_M's avatar
      Kerry_M
      Icon for Helper II rankHelper II

      This looks great! I can't wait to try it out tomorrow.when I will post an update 

    • Kerry_M's avatar
      Kerry_M
      Icon for Helper II rankHelper II

      Thank you! It worked perfectly and it is a really simple and clear solution.

  • Hi,

    Based on the table that you have shared, show the expact result which you are expecting.