Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
Solved! Go to Solution.
Something like this should work for you. Just make sure your date table is disconnected from your employee table.
Something like this should work for you. Just make sure your date table is disconnected from your employee table.
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 (
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.
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
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |