Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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))
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |