Forum Discussion
Total planned production time calculation
I was able to do this properly using the following by creating a calculated column containing the Date&ShiftId. I named this ShiftDate.
From there, I used the following DAX formula:
SUMX((DISTINCT(DataPartCell1[ShiftDate])), FIRSTNONBLANK(DataPartCell1[ShiftLength],0))
what does the part production have to do with the length of the shifts?
Even though your answer isn't getting me any closer to a solution, I'll entertain you with an answer.
Objectively, not much. But shift length directly gives me the planned production time, and with a known production rate, the expected(or theoretical, at least) yield.
There aren't always evening or night shifts, so if I want to calculate the amount of time production could've ran for X number of days/months, then I need to know how many shifts there actually were, and how long each lasts. Knowing both of those will tell me how many hours a specific machine was "expected" to run.
The database is being generated by a machine using prioprietary software from its maker, so I'm stuck with whatever it puts out and I have to work around that.
Thank you for the context. Please provide a couple more rows of sample data (to cover the "missing shift" case, for example) and indicate expected results.
No problem, sorry it took me a bit to have some time in the office for this issue. Here's a quick extract which shows the relevant columns.
Id Time SerialNumber Hour ShiftHour ShiftId Date Shift ShiftRunTime ShiftLength 16704409 2021-10-18 15:45 Modified1 15 15,75 1 2021-10-18 Day 8 7,5 16704412 2021-10-18 15:45 Modified2 15 15,75 1 2021-10-18 Day 8 7,5 16704416 2021-10-18 15:45 Modified3 15 15,75 1 2021-10-18 Day 8 7,5 16704422 2021-10-18 15:46 Modified4 15 15,766666 2 2021-10-18 Evening 0,016666 7 16704424 2021-10-18 15:46 Modified5 15 15,766666 2 2021-10-18 Evening 0,016666 7 16704425 2021-10-18 15:46 Modified6 15 15,766666 2 2021-10-18 Evening 0,016666 7 What I would need is some measure that will, for each date, sum the distinct values in the ShiftLength column.