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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

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

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