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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.