Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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)))
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.
Hi,
Many thanks. This works fine.
Best regards,
Gerben
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |