Average caseloadduration in time per colleague

[GerbenvdHazel]

Hi everyone,

I'm looking for a solution to get an average caseloadduration in time (per day) per colleague.

The end date of a trajectory can be blank. Probably this can be best solved by adding a column and transform the end date to a date in future?

Mentor		Client		Start date		End date
John		Client 1		01/07/2021		14/01/2022
John		Client 2		01/10/2021		15/01/2022
John		Client 3		06/10/2021
Rick		Client 4		31/07/2021		20/03/2022
Rick		Client 5		31/10/2021		21/03/2022
Rick		Client 6		05/11/2021		10/01/2022

The measue /outcome should be:

Date		Client 1		Client 2		Client 3		Average in days - John		Client 4		Client 5		Client 6		Average in days - Rick
01/01/2022		184		92		92		122,7		154		62		57		91,0
02/01/2022		185		93		93		123,7		155		63		58		92,0
03/01/2022		186		94		94		124,7		156		64		59		93,0
04/01/2022		187		95		95		125,7		157		65		60		94,0
05/01/2022		188		96		96		126,7		158		66		61		95,0
06/01/2022		189		97		97		127,7		159		67		62		96,0
07/01/2022		190		98		98		128,7		160		68		63		97,0
08/01/2022		191		99		99		129,7		161		69		64		98,0
09/01/2022		192		100		100		130,7		162		70		65		99,0
10/01/2022		193		101		101		131,7		163		71				117,0
11/01/2022		194		102		102		132,7		164		72				118,0
12/01/2022		195		103		103		133,7		165		73				119,0
13/01/2022		196		104		104		134,7		166		74				120,0
14/01/2022				105		105		105,0		167		75				121,0
15/01/2022						106		106,0		168		76				122,0
16/01/2022						107		107,0		169		77				123,0
17/01/2022						108		108,0		170		78				124,0
18/01/2022						109		109,0		171		79				125,0
19/01/2022						110		110,0		172		80				126,0
20/01/2022						111		111,0		173		81				127,0

Thank you and best greetings

[v-xiaotang]

Hi @GerbenvdHazel 

In this scenario, you need to create a calendar table and then create measures (see picture below). If you have multiple clients, then you need to create multiple measures accordingly.

Take john as an example, (for Rick their calculation principles are the same)

Client1 = 
var _client="Client 1"
var _mentor="John"
var _startdate=CALCULATE(MAX('Table'[Start date]),FILTER('Table','Table'[Client]= _client &&'Table'[Mentor]=_mentor))
var _enddate= CALCULATE(MAX('Table'[End date]),FILTER('Table','Table'[Client]= _client &&'Table'[Mentor]=_mentor))
return if(MIN('calendar'[Date])<_enddate,DATEDIFF(_startdate,MIN('calendar'[Date]),DAY))

Client2 = 
var _client="Client 2"
var _mentor="John"
var _startdate=CALCULATE(MAX('Table'[Start date]),FILTER('Table','Table'[Client]= _client &&'Table'[Mentor]=_mentor))
var _enddate= CALCULATE(MAX('Table'[End date]),FILTER('Table','Table'[Client]= _client &&'Table'[Mentor]=_mentor))
return if(MIN('calendar'[Date])<_enddate,DATEDIFF(_startdate,MIN('calendar'[Date]),DAY))

Average in days - John = 
var _n=2
var _sum= [Client1]+[Client2]
return DIVIDE(_sum,_n)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[GerbenvdHazel]

Thanks for your respond. I probably haven't been clear enough. We have a variety of mentors (+/- 50) and clients (+/- 1500). My provisional solution is a table: 

crossjoin(CALENDAR(
TODAY()-730,
TODAY()+0
 ),'Clients')

and calculated column:

Caseload duration = 
if('client'[Date start]>'client'[calenderdate],blank(),
if('client'[Date end]<'client'[calenderdate],blank(),
DATEDIFF('client'[date start],'client'[calenderdate],DAY)))

[v-xiaotang · Accepted solution]

Hi  @GerbenvdHazel 

Thanks for your reply.

>>We have a variety of mentors (+/- 50) and clients (+/- 1500). 

If you have a very large number of mentors and clients, you can try this way,

(1) create the table below

(2) create 3 measures,

value = 
var _client=MIN('Table'[cli])
var _mentor=MIN('Table'[men])
var _startdate=CALCULATE(MAX('client'[Start date]),FILTER('client','client'[Client]= _client &&'client'[Mentor]=_mentor))
var _enddate= CALCULATE(MAX('client'[End date]),FILTER('client','client'[Client]= _client &&'client'[Mentor]=_mentor))
return if(MIN('Table'[Date])<_enddate,DATEDIFF(_startdate,MIN('Table'[Date]),DAY))

avg = AVERAGEX('Table',[value])

Measure = SWITCH(TRUE(),
(ISFILTERED('Table'[cli])),[value],
ISFILTERED('Table'[men]),[avg])

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[GerbenvdHazel]

Hi,

Many thanks. This works fine.

Best regards,
Gerben