Active Clients Multiple Services

[elinevans]

Here is an example of the type the data I'm using. Each client can have more than 1 service and I want to find clients active in a service within a time period (say between date x and date y) defined by a slicer on the report. The slicer is based off a date table created by using CALENDARAUTO(). So essentially I wanted to get a list of client references that have have a start date before y AND end date after x. Where x and y can be altered by a date slicer. 

I was creating a filter on my visual using:

Active Clients = IF(MIN(ServiceInfo[Start Date]) <= MAX(Datetable[Date]) && MAX(ServiceInfo[End Date]) >= MIN(Datetable[Date]),1,0)

However, this also includes clients who had a service strart and end beofre and after reporting period. 

Tried to make a disagram to show what I mean:

The Green, blue and red lines are the service times for clients green, blue, red respectively. We would want to know the client references of client blue and green and their services overlap the reporting period. But not red, but the current formula above does. 

Hopefully this makes sense.

Thanks

Elin

[elinevans]

@Anonymous 

If you are referring to: 

Active Clients = IF(MIN(ServiceInfo[Start Date]) <= MAX(Datetable[Date]) && MAX(ServiceInfo[End Date]) >= MIN(Datetable[Date]),1,0)

It doesn't cover all eventualities. If the client has a service that starts and ends before the start of the reporting period, and another that starts and ends after the reporting period ends they will still be counted eventhough they are in active in reporting period.

For example if a client had 2 services which are marked by the 2 red arrows:

Sorry for all the confusion! 

[Anonymous]

I'm talking about my measure. Did you see it? 'Cause I have a feeling you didn't...

[elinevans]

Apologies for wasting your time. I didn't apply it correctly the first time and didn't realise you could add it to a filter plane as a count. Thank you for your help!! 

[Anonymous · Accepted solution]

[# Active Clients] =
var MinReportingPeriod =
	MIN( Datetable[Date] ),
var MaxReportingPeriod =
	MAX( Datetable[Date] )
var Result =
	COUNTROWS(
		SUMMARIZE(
			CALCULATETABLE(
				ServiceInfo,
				KEEPFILTERS(
					ServiceInfo[Start Date] 
						<= MaxReportingPeriod
				),
				KEEPFILTERS(
					MinReportingPeriod
						<= ServiceInfo[End Date]
				)
			),
			ServiceInfo[Client Ref]
		)
	)
return
	Result

[elinevans]

HI @Anonymous, using the above gave a count of the acive clients but what I want is a list of the client references of those clients who are active. Am I applying it wrong? 

Thanks

Elin

[Anonymous]

A measure can't return a table, only a scalar. When you say "a list of client references", what do you actually mean in this context? All you can return from a measure that imitates a list of values is a string with the values concatenated by using the CONCATENATEX function.

You can take my code from above and instead of returning the count of active clients, you can use the function to return a list of references as explained before.

[elinevans]

Hi @Anonymous 

Sorry for not being very clear. I would like a filter to be applied to a table/matrix visual so when i put in client ref as one of the values it will only show me client references of client who are active at any point during the repoting period.

Thanks

Elin

[Anonymous]

@elinevans 

It so happens the the filter you need is already there above. Just use the measure in the Filter Pane with the condition [the measure] = 1 and you'll filter for all the active clients in the current context.