Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
62 | |
52 | |
48 |
User | Count |
---|---|
208 | |
89 | |
61 | |
59 | |
57 |