Forum Discussion

elinevans's avatar
elinevans
Icon for Helper I rankHelper I
5 years ago
Solved

Active Clients Multiple Services

  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...
DAX
Help
Help appreciated
  • Anonymous's avatar
    Anonymous
    5 years ago
    [# 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's avatar
elinevans
Icon for Helper I rankHelper I
5 years ago

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

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

  • elinevans's avatar
    elinevans
    Icon for Helper I rankHelper I
    5 years ago

    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!!