cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JessieF
Frequent Visitor

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

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

ShiftsTable.jpg

 

*Edited to include images of my tables.  

 

 

6 REPLIES 6
V-lianl-msft
Community Support
Community Support

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.

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

Any chance  you can post that as data as text?

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Aron_Moore
Solution Specialist
Solution Specialist

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?

@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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.