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.
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.
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?
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?
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
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.
Click here to read more about the November 2022 updates!
Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.
This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.