Total planned production time calculation

[icbd]

Good morning everyone.

I am using PowerBI to visualize data in a SQL database. I am trying to make a measurement that will calculate the total planned production times.

I have three different production shifts

Daytime : 7:45 to 15:45

Evening: 15:45 to 23:15

Night: 23:15 to 6:15.

Currently, in power query, I check the time stamp of all produced parts and associate them to the proper shift using a calculated conditional column on the decimal value of the hour, so that day = 1, evening = 2, night = 3. All three shifts have a 30 minute lunch break removed from them, so Day (1) = 7.5 hours, Evening (2) = 7 hours, Night (3) = 6.5 hours.

What I'd like to achieve is have a measure that will calculate the total planned production time for the amount of days selected. I'm oversimplifying for the sake of explanation but see table below, which would have the relevant information to my calculation.

Time Stamp	Date	Time	ShiftId	ShiftLength
2021-10-15 13:16:30	2021-10-15	13.266666	1	7.5
2021-10-15 16:54:12	2021-10-15	16.9	2	7
2021-10-15 00:00:19	2021-10-15	0	3	6.5

I have many more time stamps (about 25 to 30k per shift) but the general idea is that I'd like a sum of the distinct shift lengths for each date.

So if I select two days, with a Day and evening shift, I want to be getting 7.5+7+7.5+7 = 29.

I'm pretty new to PowerBI and DAX and I've been hacking away at it for a bit with little success. Any help would be greatly appreciated.

[lbendlin]

what does the part production have to do with the length of the shifts?

[icbd · Accepted solution]

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))

[icbd]

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.

[lbendlin]

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. 

[icbd]

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.