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
BDale93
Helper I
Helper I

Calculate number of working days between slicer selection and factor in Start / Term dates

Hello all,

 

I'm needing some advice on the best way to calculate the number of working days for a users between the selected slicer date range. I need to consider the employees potential employement start / termination dates as well. If a user start and or term date falls between the selected date range, then calculate only the days after the start date and or before the term date, else count all the days between the slicer range

Example 1:

Slicer Range = July 1st thru September 31st
User Start Date = July 27th
Result should be the number of days between July 27th and September 31st

 

Example 2:

Slicer Range = July 1st thru September 31st
User Term Date = July 27th
Result should be the number of days between July 1st and July 27th

Example 3:

Slicer Range = July 1st thru September 31st
User Start Date = July 27th
User Term Date = September 15th
Result should be the number of days between July 27th and September 15th

 

Any help is appreciated!

1 ACCEPTED SOLUTION
CoreyP
Solution Sage
Solution Sage

Something like this should work for you. Just make sure your date table is disconnected from your employee table.

CoreyP_0-1695098919047.png

 

View solution in original post

4 REPLIES 4
CoreyP
Solution Sage
Solution Sage

Something like this should work for you. Just make sure your date table is disconnected from your employee table.

CoreyP_0-1695098919047.png

 

CoreyP,

 

This is great, thank you. One last piece to this which I didnt add into the Examples and current logic is not working. The way my report is set up is the month slicer that needs to be used summarizes the entire month. So 7/1/2023 is the entire month, not just the July 1st. Therefore if a consumer selects 7/1/2023 instead of a range the same logic needs to apply as it would a entire date range. Thats not the case, only works for termed employees during that month. I've tried using an additional variable for individual month selection (

VAR SlicerValue = SELECTEDVALUE('Date Table'[Month])) but cannot seem to get the logic to work. Any additional help would be aprpeciated.

I'm not sure I understand what you mean. When I use a month slicer, it still works. The date range when a month is selected is from the 1st to the end of the month.

CoreyP_0-1695243564190.png

 

I had made a mistake and was using 'Month' field when creating the slicer min / max. Switched to 'Date' and it's now working perfectly. Thanks again

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.