Count total dates from slicer, filtering out weekends

[aashton]

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)

[FreemanZ]

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"

    )

)

[aashton]

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:

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

RETURN

COUNTROWS(

    FILTER(

        ALL(DateTable),

        DateTable[Date]>=_FirstDay

           &&DateTable[Date]<=_LastDay

           &&DateTable[Weekend Flag] = _WeekendFlag

    )

)

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

[aashton]

@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?

[FreemanZ]

hi @aashton 

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

[aashton]

@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

)

)

[FreemanZ]

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

)

)

[aashton]

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

[aashton · Accepted solution]

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

&& dim_date[Weekend Flag] IN _WeekendFlag