Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper V
3 years ago
Solved

Count 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...
  • aashton's avatar
    aashton
    3 years ago

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

     

    && dim_date[Weekend Flag] IN _WeekendFlag
aashton's avatar
aashton
Icon for Helper V rankHelper V
3 years ago

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's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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's avatar
    aashton
    Icon for Helper V rankHelper V
    3 years ago

    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's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi aashton 

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

      • aashton's avatar
        aashton
        Icon for Helper V rankHelper V
        3 years ago

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