cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Total planned production time calculation

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.

1 ACCEPTED SOLUTION
Helper I

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

5 REPLIES 5
Super User

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

Helper I

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

Helper I

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.

Super User

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.

Helper I

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors