Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am new to Power Bi and need some help in trying to be able to calculate "active clients" by program each month. I am using a connection directly to a CMS system which is providing me with the total list of clients. In the table is a "program start date", a "program end date" and the program name in columns. It looks similar to the below table. I think I can easily get the program information through filters but I am not sure how to get the active client number on a monthly basis. I also have a separate date table.
Thanks in advance
Client | program_name | program_start_date | program_end_date |
1 | A | 1/07/2019 | 20/12/2019 |
2 | B | 27/07/2019 | 30/08/2019 |
3 | C | 1/01/2018 | 9/11/2018 |
4 | D | 1/06/2018 | 22/02/2019 |
5 | D | 31/12/2018 | 5/03/2019 |
6 | A | 25/01/2019 | |
7 | B | 30/11/2018 | 6/10/2019 |
8 | C | 20/01/2019 | 5/05/2019 |
9 | E | 5/02/2019 | |
10 | A | 20/02/2019 | 14/09/2019 |
11 | B | 9/03/2019 | |
12 | E | 22/04/2019 | 20/05/2019 |
13 | C | 1/09/2019 | |
14 | D | 3/10/2017 | 6/06/2018 |
15 | B | 22/12/2017 | 18/06/2018 |
16 | B | 9/05/2018 | 30/11/2018 |
17 | A | 10/03/2019 | 29/07/2019 |
18 | D | 8/08/2019 | |
19 | C | 5/05/2018 | 9/09/2019 |
20 | E | 30/06/2019 | |
21 | E | 9/09/2019 |
Solved! Go to Solution.
hi, @Anonymous
Here is the same post for you refer to:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
For your case, just add a conditional that if there is no value in "program end date" should it be "Today" or other default date.
Best Regards,
Lin
hi, @Anonymous
Here is the same post for you refer to:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
For your case, just add a conditional that if there is no value in "program end date" should it be "Today" or other default date.
Best Regards,
Lin
Hi, @Anonymous. If I understand your goal, you can use the FILTER function together with a DATE table in order to get just the active clients between the dates. Try something like this:
Active Clients = CALCULATE ( COUNT ( NameTable[Clients] ); FILTER ( NameTable; NameTable[program_start_date] >= MIN ( Date[Date] ) && NameTable[program_end_date] <= MAX ( Date[Date] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |