Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Kerry_M
Helper II
Helper II

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
2 ACCEPTED SOLUTIONS
ThxAlot
Super User
Super User

Easy enough,

ThxAlot_1-1728856336758.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The expected result would be 5 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1728874231368.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you! This works well.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ThxAlot
Super User
Super User

Easy enough,

ThxAlot_1-1728856336758.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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

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

lbendlin
Super User
Super User

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

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.