Forum Discussion
aashton
Helper V
Helper VCount total dates from slicer, filtering out weekends
Hello, I have a report that has a date slicer allowing a user to select a date range. I also have a Weekend Flag filter, choices are Weekday, Weekend or All, allowing them to choose which days to i...
FreemanZ
Super User
Super Userhi aashton
Supposing you have a qualified Date Table, try like:
Total Days =
VAR _FirstDay =
CALCULATE(
MIN('Case Details'[Surgery Date]),
ALLSELECTED('Case Details'[Surgery Date])
)
VAR _LastDay =
CALCULATE(
MAX('Case Details'[Surgery Date]),
ALLSELECTED('Case Details'[Surgery Date])
)
RETURN
COUNTROWS(
FILTER(
ALL(DateTable),
DateTable[Date]>=_FirstDay
&&DateTable[Date]<=_LastDay
&&DateTable[Weekend Flag] = "No"
)
)
aashtonHelper V
Helper VThank you yes, that looks like it will work. But I need the filter do be dependent on what they pick on the Weekend Flag filter, so I can't hardcode it just for weekdays:
- FreemanZ
hi aashton
then try like:
Total Days =VAR _FirstDay =CALCULATE(MIN('Case Details'[Surgery Date]),ALLSELECTED('Case Details'[Surgery Date]))VAR _LastDay =CALCULATE(MAX('Case Details'[Surgery Date]),ALLSELECTED('Case Details'[Surgery Date]))VAR _WeekendFlag =SELECTEDVALUE(DateTable[Weekend Flag])RETURNCOUNTROWS(FILTER(ALL(DateTable),DateTable[Date]>=_FirstDay&&DateTable[Date]<=_LastDay&&DateTable[Weekend Flag] = _WeekendFlag))p.s. consider @someone to continue a discussion.