Forum Discussion
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...
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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)
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.