Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
aashton
Helper V
Helper V

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)

 

1 ACCEPTED SOLUTION

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

 

&& dim_date[Weekend Flag] IN _WeekendFlag

View solution in original post

9 REPLIES 9
FreemanZ
Super User
Super User

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

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:

 

aashton_0-1675371592595.png

 

 

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.

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

hi @aashton 

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

@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
)
)
aashton_0-1675390605556.pngaashton_1-1675390621290.png

 

 

 

aashton_2-1675390635523.png

 

 

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

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

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

 

&& dim_date[Weekend Flag] IN _WeekendFlag

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.