Calculate total count of clients at the end of each month, quarter, and fiscal year

[nsiu85]

Hi,

I am quite new to Power BI and using DAX. How do I go about calculating the total number of client at the end of each month, quarter and fiscal year. The criteria to give a total number of client at the end of each month would require:

• new clients within the month, and
• client that already existed but still remain active at the clinic, and
• client exiting within the month

Have include a very small sample data set as an example and the result of it

Client ID	Start Date	Close	Exit Date
109	27/11/2019	Y	3/12/2020
107	27/11/2019	N
108	27/11/2019	Y	3/02/2021
113	9/01/2020	Y	11/02/2021
112	15/01/2020	Y	18/07/2020
111	15/02/2020	N
114	15/03/2020	N
115	15/05/2020	Y	8/10/2020
117	16/05/2020	Y	20/12/2020
116	16/08/2020	Y	20/12/2020
123	6/10/2020	Y	6/12/2020
122	6/10/2020	N
121	6/12/2020	Y	28/01/2021
122	21/03/2021	N

In Power BI should have something like this:

2019
Month	New	Existing	Exit
Nov	3
Dec		3

2020
Month	New	Existing	Exit
Jan	2	3
Feb	1	5
Mar	1	6
Apr		7
May	2	7
Jun		9
Jul		9	1
Aug		9
Sep		9
Oct	2	9	1
Nov		10
Dec	1	10	5

2021
Month	New	Existing	Exit
Jan		6	1
Feb		5	1
Mar	1	4
Apr		5

Any advice or help would be great.

[nsiu85]

Hi @tamerj1 

I had a go in using the DAX functions you provided and make the necessary changes to reflect what is on my table of data and columns. However, when I select existing and exit measure no data is shown. It is presented blank

[tamerj1]

Hi @nsiu85 

please provide details on these changes and advise what are the differences in your model. Do you have relationships with the date table?

[nsiu85]

Yes I have relationships link to the date table. I have Date with Episode Start Date (aka start date) linked as one to many relationship.

Here is the coding I used from what you have provided and replaced start date with Episode Start Date and exit date with date closed. Not sure what is happening here. 

[tamerj1]

@nsiu85 
You can just delete or deactivate he relationship. If for any reason you wish to keep it, you can follow the attached updated sample file

New = 
CALCULATE ( 
    COUNTROWS ( 'Table' ),
    TREATAS ( VALUES ( 'Date'[Date] ), 'Table'[Start Date] ),
    ALL ( 'Date' )
)

Existing = 
VAR MaxDate = MAX ( 'Date'[Date] )
VAR MinDate = MIN ( 'Date'[Date] )
RETURN
    COUNTROWS ( 
        FILTER ( 
            CALCULATETABLE ( 'Table', ALL ( 'Date' ) ),
            'Table'[Start Date] <= MinDate
                && COALESCE ( 'Table'[Exit Date], TODAY ( ) ) >= MaxDate
        )
    )

Exit = 
IF ( 
    NOT ISEMPTY ( 'Date' ),
    CALCULATE ( 
        COUNTROWS ( 'Table' ),
        TREATAS ( VALUES ( 'Date'[Date] ), 'Table'[Exit Date] ),
        ALL ( 'Date' )
    )
)

[nsiu85]

Thank you @tamerj1 let me have a play with the big data set I have and see if I get what I need from the report.

[tamerj1]

Hi @nsiu85 
Please refer to attached sample file with the solution

New = 
CALCULATE ( 
    COUNTROWS ( 'Table' ),
    TREATAS ( VALUES ( 'Date'[Date] ), 'Table'[Start Date] ) 
)

Exit = 
CALCULATE ( 
    COUNTROWS ( 'Table' ),
    TREATAS ( VALUES ( 'Date'[Date] ), 'Table'[Exit Date] ) 
)

Existing = 
VAR MaxDate = MAX ( 'Date'[Date] )
VAR MinDate = MIN ( 'Date'[Date] )
RETURN
    COUNTROWS ( 
        FILTER ( 
            'Table',
            'Table'[Start Date] <= MinDate
                && COALESCE ( 'Table'[Exit Date], TODAY ( ) ) >= MaxDate
        )
    )