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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors