Active Clients in time frame

[LeahS]

I have the below data.  I'm trying to create a measure that will be tied to my date slicer (which filters by month & year).  I want the measure to count how many active clients there were in the month selected based on client start date and end date.  I tried to use a var_date syntax I saw on-line, but since I'm new to power BI, I couldn't make it work with 2 conditions.  Does anyone have any ideas on what might work?  It would also be gre

Client	Start_Date	Discharge_date
Le St	10/17/2022
Pe Se	7/14/2022
Ch Pa	5/1/2023	6/1/2023
Zo Gi	2/13/2023	7/2/2023
Be Sp	12/21/2022	3/15/2023
Fa Gi	11/20/2022

at if I could calculate current active clients, but I couldn't get ISBLANK() to work for me.

[LeahS]

Thanks!  That worked.  And the syntax is easy enough to understand that I can adapt it for other measures.

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[Greg_Deckler]

@LeahS 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[LeahS]

@Greg_Deckler 

The first one works mostly, but I can't get the count right.  I think my column names are off.  Below is the measure I created.  Can you tell me where I went wrong?

Active Clients L = VAR tmpclients = ADDCOLUMNS('Lincoln Client Info',"Active",IF(ISBLANK([Discharge Date]),TODAY(),[Discharge Date]))

VAR tmpTable =  

SELECTCOLUMNS(

    FILTER(

        GENERATE(

            tmpclients,

            'Date Table'

        ),

        [Date] >= [Start Date] &&

        [Date] <= [Discharge Date]

    ),

    "Active",[Start Date],

    "Date",[Date]

)

VAR tmpTable1 = GROUPBY(tmpTable,[Active],"Count",COUNTX(CURRENTGROUP(),[Date]))

RETURN COUNTROWS(tmpTable1)

[v-rzhou-msft · Accepted solution]

Hi @LeahS ,

I suggest you to create a measure as below to count the active clients.

Count Active Clients =
VAR _SELECTSTART =
    MIN ( 'Date'[Date] )
VAR _SELECTEND =
    MAX ( 'Date'[Date] )
RETURN
    COUNTX (
        FILTER (
            'Lincoln Client Info',
            'Lincoln Client Info'[Start Date] <= _SELECTEND
                && OR (
                    'Lincoln Client Info'[Discharge Date] >= _SELECTSTART,
                    'Lincoln Client Info'[Discharge Date] = BLANK ()
                )
        ),
        [Client]
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.