Hi Everyone,
I have a measure, this measure is to count the number of assessed, is criteria is to use the very first assessment held date within the selection period for that unique case. For example, if that case has two assessment held, one is in April, one is in May, if I select April, this will count as 1, if I select May, this will not count into any since the very 1st assessment happened in April.
This is my measure:
count of assessed =
CALCULATE(
DISTINCTCOUNT('b_Case'[CaseNumber])
, 'f_Calls'[status] = "Held" && ('f_Calls'[CallTypeId] = 5 || 'f_Calls'[CallTypeId] = 8 ),
FILTER(b_Case, b_Case[LeadSite] = "A" && b_Case[ServiceRequestType] = "Referral to Program"),
USERELATIONSHIP(d_date[Date],f_Calls[DateStart]))
And I have another measure to calculate the cumulative count of assessed year to date, below is my query:(My start date is 2023-04-01, I want to show a line graph to show the cumulative count of assessed from 04-01), bacause I use 3 measure as a filter, so that is why I use a switch function.
CumulativeCurrentValue =
VAR StartDate =
DATE( 2023, 4, 1 )
VAR SelectedDate = MAX( 'd_date'[Date] ) //Replace 'd_date'[Date] with the actual date column in your date table
RETURN
CALCULATE(
SUMX(
FILTER(
ALL( 'd_date'[Date] ),
'd_date'[Date] >= StartDate
&& 'd_date'[Date] <= SelectedDate
),
SWITCH(
MIN( 'Current Measure'[Number] ),
1,[Count of Transfers],
2,[count of assessed],
3,[Count of Enrollments]
)
),
DATESYTD(
'd_date'[Date],
"4/1"
)
)
