Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Active Clients by Month

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

 

Clientprogram_nameprogram_start_dateprogram_end_date
1A1/07/201920/12/2019
2B27/07/201930/08/2019
3C1/01/20189/11/2018
4D1/06/201822/02/2019
5D31/12/20185/03/2019
6A25/01/2019 
7B30/11/20186/10/2019
8C20/01/20195/05/2019
9E5/02/2019 
10A20/02/201914/09/2019
11B9/03/2019 
12E22/04/201920/05/2019
13C1/09/2019 
14D3/10/20176/06/2018
15B22/12/201718/06/2018
16B9/05/201830/11/2018
17A10/03/201929/07/2019
18D8/08/2019 
19C5/05/20189/09/2019
20E30/06/2019 
21E9/09/2019 
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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