Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey there!
Got a little Problem with some data.
For better understanding here an example:
I've got a table with time stamps of machined parts. The time stamp tells me, when the part was finished. I would like to take the time stamp of the last part as start time for the next part. So I will get a time range, during which time a part was produced. In a second tabel I can compare the time ranges to error messages. That I already solved.
Here is an example for the tabel, the last column should be calculated.
machine | part | End Time | Start Time |
1 | 1 | 07:30 | Blank |
2 | 2 | 07:29 | 07:22 |
1 | 3 | 07:37 | 07:30 |
1 | 4 | 07:44 | 07:37 |
2 | 5 | 07:36 | 07:29 |
1 | 6 | 07:51 | 07:44 |
2 | 7 | 07:22 | Blank |
1 | 8 | 07:58 | 07:51 |
I tried to find the Min-Value of a filtered list where the row of the actual value is excepted. But I didn't get to a solution.
Hopefully someone can help!
Solved! Go to Solution.
So a slight modification to the code then:
Start time = VAR _prevTime = 'Table'[End Time] VAR _machine = 'Table'[machine] RETURN CALCULATE ( MAX ( 'Table'[End Time] ); FILTER ( 'Table'; 'Table'[machine] = _machine && 'Table'[End Time] < _prevTime ) )
And just to be sure, you are trying to create a calculated column, right?
This works on the sample data you provided. If you have trouble implementing it in your model, perhaps post a more realistic dataset?
Hi,
try this
Start time = CALCULATE ( MAX ( 'Table'[End Time] ); FILTER ( 'Table'; 'Table'[machine] = EARLIER ( 'Table'[machine] ) && 'Table'[part] < EARLIER ( 'Table'[part] ) ) )
Hey,
thanks for the reply.
Unfortunately it doesn't work. I've tried the formula with the exact given table in my question. But I got in the start-column the same time as in the end column.
By the way, the part number isn't meant as a rank. it is only a number.
So a slight modification to the code then:
Start time = VAR _prevTime = 'Table'[End Time] VAR _machine = 'Table'[machine] RETURN CALCULATE ( MAX ( 'Table'[End Time] ); FILTER ( 'Table'; 'Table'[machine] = _machine && 'Table'[End Time] < _prevTime ) )
And just to be sure, you are trying to create a calculated column, right?
This works on the sample data you provided. If you have trouble implementing it in your model, perhaps post a more realistic dataset?
That's it!
Thanks a lot!