Forum Discussion
Helper IDetermine Active Clients At Any Point in Time
Hello,
I'm pretty new to Power BI and DAX, and am trying to wrap my brain around how to do something. We have clients that come in and out of our system and need a way to determine if they are active on certain dates or within certain timeframes. For example, a user selects May 1, 2022 and we want to show how many active clients we had on that date. We have enrollment dates and exit dates, but not all clients have exit dates as they are still active, so we have to account for that too. We would also want to be able to do this for entire months and years too, to show how many unique active clients we had during these larger time frames too. I know how to "hardcode" this to a specific date, but just don't know how to make it happen with dynamic date selection. Any help or suggestions would be appreciated. 🙂
Thanks! It took me a while and quite a lot of tries and versions, but I made it work!
Sure! I use this calculation all of the time now. It usually looks something like this:
ActiveClients =VAR MinDate = FIRSTDATE('Date'[Date])VAR MaxDate = LASTDATE('Date'[Date])RETURNCALCULATE (DISTINCTCOUNT('CombinedTables'[PersonalID]),'CombinedTables'[EnrollmentEnd]> MinDate,'CombinedTables'[EnrollmentStart] <= MaxDate)
10 Replies
- parry2k
Super User
heatherkw there are lot of blogs/videos about this. if you search for employee head count (common scenario) DAX, you will find a solution.
Here is one, I'm not endorsing any solution because I don't know how they will perform. Here is one example link Employee Head Count Over Time | Power BI Exchange (pbiusergroup.com)
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
- Ashish_Mathur
Hi,
Share some data to work with.
heatherkwClientID EnrollmentID EnrollmentStart EnrollmentEnd 1 12347 1/1/2019 5/31/2019 1 12349 5/31/2019 2 12352 12/22/2019 6/1/2020 3 12354 1/15/2020 4 12357 2/24/2020 5/31/2020 4 12360 5/31/2020 9/30/2020 4 12363 12/30/2020 5 12358 4/3/2020 4/4/2020 5 12359 4/4/2020 6 12350 6/2/2019 12/31/2019 6 12353 12/31/2019 6/1/2020 7 12361 7/4/2020 8 12351 6/2/2019 9 12362 12/2/2020 10 12345 3/2/2018 6/4/2018 10 12346 9/2/2018 2/3/2019 10 12348 5/2/2019 12/2/2019 10 12355 2/4/2020 2/5/2020 10 12356 2/5/2020 Here is some made up data. I did look around at some other threads and thought I found a solution, but it didn't account for a person having multiple enrollments over a time period, and only seemed to allow counts on a specific date, rather than also looking at say, annual counts. It's very common for our clients to have multiple enrollments, but we only want distinct counts based on their client ids.
- Ashish_Mathur
Hi,
How can the enrollment start date be the same as the enrollment end date (see ClientID 1). If the enrollment ends on 5/31/2019, shouldn't the next one start frp, 1/6/2019? If my understanding is correct, then please share revised data.
