Active Clients by Month

[Anonymous]

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

[v-lili6-msft · Accepted 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

[Anonymous]

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] )
    )
)