Forum Discussion
Need help coonecting caselaod status spanning date range
I have caseloads with differetn statuses ie compelted active waitlist i have the data connected to custom date table with quarters when i look into a matrix visualization i want to see if someone i...
okay this is what i went with i created a measuer
Caseload Progress =
VAR MaxDate = MAX('Date'[Date])
VAR MinDate = MIN('Date'[Date])
RETURN
CALCULATE(
COUNTROWS(
FILTER('Individual Plans',
'Individual Plans'[startdateserviceplan]<=MaxDate&&
'Individual Plans'[status dates]>=MinDate)),
CROSSFILTER('Date'[Date],'Individual Plans'[status dates],None))
this measure works great...only problem is countrows is showing all rows...there is a few cases with the same id...if the id is the same i woudl want the earliest startdate and maxstatus date..how would i go about that
also the date table is connecteed to status dates which has a formula as well
so it basically is showing from start to the status date depednign ont he status...let me know what you think
this measure works great...only problem is countrows is showing all rows...there is a few cases with the same id...if the id is the same i woudl want the earliest startdate and maxstatus date..how would i go about that
also the date table is connecteed to status dates which has a formula as well
status dates =
IF(
'Individual Plans'[status] = "Active",
TODAY(),
IF(
'Individual Plans'[status] = "Waitlist",
'Individual Plans'[dateentered],
IF(
'Individual Plans'[status] = "Completed",
'Individual Plans'[enddateserviceplan],
IF(
'Individual Plans'[status] = "Closed",
'Individual Plans'[senddateserviceplan],
'Individual Plans'[dateentered] // For any other status, use 'dateentered'
)
)
)
)
so it basically is showing from start to the status date depednign ont he status...let me know what you think
this measure works exactly the way i want for time intellgience event progress
Caseload Progress =
VAR MaxDate = MAX('Date'[Date])
VAR MinDate = MIN('Date'[Date])
RETURN
COUNTROWS(
SUMMARIZE(
FILTER(
'Individual Plans',
'Individual Plans'[startdateserviceplan] <= MaxDate &&
'Individual Plans'[status dates] >= MinDate
),
'Individual Plans'[id],
"MinStartDate", MIN('Individual Plans'[startdateserviceplan]),
"MaxStatusDate", MAX('Individual Plans'[status dates])
)
)