Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I am trying to calculate the number of occasions of absence. My table has 2 columns, start date and end date. I also have a date table. I would like to add a date range slicer to filter. The criteria is if the start date >= the start date on the slicer and if the (end date <= the end date on the slicer or if the end date is blank)
Can someone help with the syntax please? thanks
Solved! Go to Solution.
Hi Charlie
THanks for this. Your solution didn't work correctly for me but I found a solution using the following
Count of Absence7 =
CALCULATE(
DISTINCTCOUNT('AbsenceViewOccasions'[index]),
FILTER(
'AbsenceViewOccasions',
'AbsenceViewOccasions'[Start_Date] <= LASTDATE(Dates[Date]) &&
('AbsenceViewOccasions'[End_Date] >= FIRSTDATE(dates[Date]) || ISBLANK('AbsenceViewOccasions'[End_Date]))
)
)
Please create a measure by using the expression below.
Measure = VAR selectedstartdate = MIN ( 'Date'[Date] ) VAR selectedenddate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTA ( Table1[StartDate] ), FILTER ( ALL ( Table1 ), ( Table1[StartDate] >= selectedstartdate && Table1[EndDate] <= selectedenddate ) || ISBLANK ( Table1[EndDate] ) ) )
Regards,
Charlie Liao
Hi Charlie
THanks for this. Your solution didn't work correctly for me but I found a solution using the following
Count of Absence7 =
CALCULATE(
DISTINCTCOUNT('AbsenceViewOccasions'[index]),
FILTER(
'AbsenceViewOccasions',
'AbsenceViewOccasions'[Start_Date] <= LASTDATE(Dates[Date]) &&
('AbsenceViewOccasions'[End_Date] >= FIRSTDATE(dates[Date]) || ISBLANK('AbsenceViewOccasions'[End_Date]))
)
)