Forum Discussion

LeahS's avatar
LeahS
Frequent Visitor
3 years ago
Solved

Active Clients in time frame

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 mont...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

LeahS's avatar
LeahS
Frequent Visitor
3 years ago

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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.