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, 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]))
)
)
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |