Forum Discussion
Shaping Date Using a Shift Offset?
I am working with employee pay records and I have employees where their shift overlaps days.
For example: I am scheduled to work Thursday 3/19 but I come in at 6PM Wednesday and work till 6AM Thursday. They can input time anywhere in that range but I want to be able to use a date filter of 3/19.
I would want all these records returned if I selected a slicer of 2/11
| EMP_ID | START_TIME | END_TIME | SHIFT_CODE | HOURS |
| 576 | 2/10/2020 22:00 | 2/11/2020 2:00 | 20 | 4 |
| 576 | 2/11/2020 4:30 | 2/11/2020 5:30 | 20 | 1 |
| 576 | 2/11/2020 5:30 | 2/11/2020 6:00 | 20 | 0.5 |
| 576 | 2/11/2020 2:30 | 2/11/2020 4:30 | 20 | 2 |
| 576 | 2/11/2020 6:00 | 2/11/2020 6:30 | 20 | 0.5 |
Based on the shift code being 20 which means they came in early
Here is the shift table I have to work with
| SHIFT_CODE | SHIFT_START | SHIFT_END |
| 20 | 1800 | 0630 |
| 17 | 0000 | 2355 |
| 10 | 0600 | 1830 |
Hope I explained this well enough!
6 Replies
I'm not understanding the problem well enough. If you want all of those records in your sample data to be returned if you select Feb 11, then just use the End Date column for your slicer. If that won't work, explain why and how you'd logically determine what should be pulled, then we can assist with measures, calculated columns, or Power Query logic to assist in getting what you need.
Hey edhans ,
Its possible that the records have a start time and end time of feb 10 and I still would want them shown when selecting Feb 11 in the fitler.
It really correlates to all row records that have a shift value of 20. In the shift table you'll see shift code 20 starts at 1800 the day before and ends 0630 on the current day. I'm sorry if I can't explain this any better. Let me generalize it a bit better.
Shift Code 20 = Range is 1800 Previous Day - 0600 Current Day
If I select as an example 2/14 as a Date Filter and employee 123
I want to return all records that have a start time between 2/13 1800 - 2/14 0600
I might be able to just right an if statement on the start_time column
if shift =20 & start_time > 18:00 & start_time < 2359 then start_time.date + 1 else start_time.date
ok. I've got to head out for a bit but the model exists in my PBIX file. I think Power Query is the best place to set this up, but Greg_Deckler 's solution may work for you. I'll check back in a bit.
Hmm, I cover some Shift stuff in my new book DAX Cookbook. However, in your case I am not sure it needs to be super complex:
Perhaps
Measure = VAR __Start = INT(MAX('Table'[START_TIME])) VAR __End = INT(MAX('Table'[END_TIME)) VAR __Date = INT(SELECTEDVALUE('Calendar'[Date]) RETURN IF(__Date = __Start || __Date = __End,1,0)Defintely gotta check out your book! I've been using PowerBI for years and sometimes I hit a brick wall. Would love to learn a new approach on things. What do you think of this approach. It seems to be working based on what I can see
Column = var start_shift = 'Table Pay'[start_time_date] + TIME(18,00,00) var end_shift = 'Table Pay'[start_time_date] + TIME(23,59,59) var stmt = IF('Table Pay'[SHIFT_CODE] = 20 && 'Table Pay'[START_TIME] >= start_shift && 'Table Pay'[START_TIME] <= end_shift, 'Table Pay'[start_time_date] +1, 'Table Pay'[start_time_date]) return stmtThis forces all records that have an overlapping shift and in a range of 1600-2359 to +1 to that date. This should then give a me a column I can use in a relationship with a main Date table to filter off of.
- I'd have to test it be certain, but I'm a big fan of things that work! 🙂
If it is working for you and you can do what you need to do, then it sounds like the way to go.
It's tough to get to specific answers for people because it is hard to convey all of the context involved in their particular situations so you are the ultimate judge of what works and what doesn't!
