Lookup Shift based on time of entry being between Shift start and end time

[JessieF]

I have a table of data called Workcenter Output, with quantity of products posted to the system. Time and Date of posting are in separate columns.  A separate table Shifts tells me Shift 1, 2 or 3 and the start and end time.

I want to tie Workcenter Output Time of posting with Shifts to track Output quantity per shift. Workcenter Output

Part of a possible calculation issue is that Shift start times occur within the prior shift. So that is why I need to do an AND calculation. The Time of posting must be Greater than or equal to Shift Start AND Less than or equal to Shift End.

I've attempted Lookups, CalculateTable with Filter and I'm struggling with the syntax. If I use CalculateTable and MIN or MAX on Shift table, I'll get a result, but only one shift and the other rows will be blank. I need to reference all the shifts.

*Edited to include images of my tables.  

[V-lianl-msft]

Hi @JessieF ,

You can refer to this thread:https://community.powerbi.com/t5/Desktop/Assigning-a-Time-to-a-Time-Period/m-p/966667 

If the problem persists,could you please share a sample PBIX file with dummy data?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JessieF]

@V-lianl-msft  I am working through reading the linked articles. I did create a Time Table as suggested.   I am not certain yet if the solution resolves, but I am working on comprehension on my end.  I will follow-up after resolution. 
I edited my original post to show tables.  I also realized after I posted, that I wasn't capturing shifts properly.  I have 5 cost centers, that have employees working in shifts.  1 Cost Center has different shifts than the others.  So I modified by Shift table to indicate which cost center it belongs to.  
I'm afraid I'm over complicating my tables, but unsure what direction to take so I'm trying a few scenarios.

[Greg_Deckler]

Any chance  you can post that as data as text?

[Greg_Deckler]

I cover Shifts in my book, DAX Cookbook that will be released next week. But, I would need more information to get you an answer. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Aron_Moore]

There's something I don't get. If each of the three shifts cover 24 hours, wouldn't a posting of a product to a specific time be attributed to every shift? (Since a shifts contain all hours)

Can you produce some sample tables/data?

[JessieF]

@Aron_Moore My apologies for poorly wording this. I meant to convey that no posting would occur that is not within 1 of the 3 shift options.  Shift1 is 7am - 4pm, Shift 2 is 3:30pm - 11:30pm, Shift 3 is 11pm - 7am. 

I will attempt to edit to add examples. I tried to attach them yesterday and kept getting an error about incorrect HTML in my posting and the images would not attach. This is my first posting, sorry for the confusion.

Jessie