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

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

Reply
icbd
Helper I
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 StampDateTimeShiftIdShiftLength
2021-10-15 13:16:302021-10-1513.26666617.5
2021-10-15 16:54:122021-10-1516.927
2021-10-15 00:00:192021-10-15036.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

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

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

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

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

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. 

 

IdTimeSerialNumberHourShiftHourShiftIdDateShiftShiftRunTimeShiftLength
167044092021-10-18 15:45Modified11515,7512021-10-18Day87,5
167044122021-10-18 15:45Modified21515,7512021-10-18Day87,5
167044162021-10-18 15:45Modified31515,7512021-10-18Day87,5
167044222021-10-18 15:46Modified41515,76666622021-10-18Evening0,0166667
167044242021-10-18 15:46Modified51515,76666622021-10-18Evening0,0166667
167044252021-10-18 15:46Modified61515,76666622021-10-18Evening0,0166667

What I would need is some measure that will, for each date, sum the distinct values in the ShiftLength column.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.