Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JessieF
Advocate I
Advocate I

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.