topic Re: Count total dates from slicer, filtering out weekends in DAX Commands and Tips

Count total dates from slicer, filtering out weekends

aashton — Thu, 02 Feb 2023 20:42:41 GMT

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 include. I need to count the total days dependent on what they pick. I've used this for Total Days, and it works fine, but I don't know how to incorporate in the Weekend filter to only count Weekdays if needed:

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
CALCULATE((DATEDIFF(FirstDay, LastDay, DAY)) + 1)

Re: Count total dates from slicer, filtering out weekends

FreemanZ — Thu, 02 Feb 2023 20:57:28 GMT

hi 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"

)

Re: Count total dates from slicer, filtering out weekends

aashton — Thu, 02 Feb 2023 21:00:25 GMT

Thank 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:

Re: Count total dates from slicer, filtering out weekends

FreemanZ — Thu, 02 Feb 2023 21:24:14 GMT

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])

RETURN

COUNTROWS(

FILTER(

ALL(DateTable),

DateTable[Date]>=_FirstDay

&&DateTable[Date]<=_LastDay

&&DateTable[Weekend Flag] = _WeekendFlag

)

p.s. consider @someone to continue a discussion.

Re: Count total dates from slicer, filtering out weekends

aashton — Thu, 02 Feb 2023 21:43:40 GMT

FreemanZ Thank you so much for your help. Sorry one more thing...what if they pick both Weekend and Weekday because they want to see everyday?

Re: Count total dates from slicer, filtering out weekends

FreemanZ — Fri, 03 Feb 2023 01:38:55 GMT

hi aashton

if the user doesn't pick any item in the slicer, the measure shall calculate for everyday.

Re: Count total dates from slicer, filtering out weekends

aashton — Fri, 03 Feb 2023 02:17:41 GMT

FreemanZ Ohhhh...I must have done something wrong, it's not working like that:

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(dim_date[Weekend Flag])

RETURN

COUNTROWS(

FILTER(

ALL(dim_date),

dim_date[act_date]>=FirstDay && dim_date[act_date]<=LastDay && dim_date[Weekend Flag]=_WeekendFlag

)

Re: Count total dates from slicer, filtering out weekends

FreemanZ — Fri, 03 Feb 2023 02:26:41 GMT

hi aashton

Noticed the slicer is feeded with the [Day of Week] column, 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(dim_date[Day of Week])

RETURN

COUNTROWS(

FILTER(

ALL(dim_date),

dim_date[act_date]>=FirstDay && dim_date[act_date]<=LastDay && dim_date[Weekend Flag]=_WeekendFlag

)

Re: Count total dates from slicer, filtering out weekends

aashton — Fri, 03 Feb 2023 13:05:06 GMT

FreemanZ The Day of the Week is just the title on put on the box, it is the Weekend Flag field....

Re: Count total dates from slicer, filtering out weekends

aashton — Fri, 03 Feb 2023 13:11:57 GMT

FreemanZ I was able to get it working...I changed it to "IN":

&& dim_date[Weekend Flag] IN _WeekendFlag