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
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 |
Solved! Go to Solution.
Easy enough,
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hi,
Based on the table that you have shared, show the expact result which you are expecting.
Hi,
Based on the table that you have shared, show the expact result which you are expecting.
The expected result would be 5
Thank you! This works well.
You are welcome.
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |