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

 

  • 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

9 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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'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.