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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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

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.