Forum Discussion

thmonte's avatar
thmonte
Helper IV
6 years ago

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 Th...
Need Help
thmonte's avatar
thmonte
Helper IV
6 years ago

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

 

 

edhans's avatar
edhans
Community Champion
6 years ago

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.