Shaping Date Using a Shift Offset?

[thmonte]

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!

[Greg_Deckler]

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)

[thmonte]

@Greg_Deckler 

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 stmt

This 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.

[Greg_Deckler]

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!

[edhans]

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.

[thmonte]

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]

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.