The first measure is Availability. To calculate Availability, we start by getting all the rows from the R04_Availability table that are currently in context and store these rows in the __Table table variable. Now, we add two columns to this table, __Next and __Uptime, in order to create the new table variable, __Table1.
To calculate the uptime of machines between failures, we must know the time of the next failure. This is the purpose of the __Next column. For each failure event (row), __Next computes the time of the next failure after the current row. To accomplish this, we use FILTER to return only the rows from __Table where MachineName is equal to the current row's MachineName (EARLIER) and RepairStarted is greater than the current row's RepairStarted (EARLIER). Then, we can simply take the minimum RepairStarted time from this filtered set in order using MINX, in order to arrive at the start time of the next subsequent failure.
Now we can add our second column, __Uptime. For the __Uptime calculation, we check to see if the value for __Next is blank (ISBLANK). If __Next is blank, then we are at the end of our data since there is no subsequent failure in the data. Thus, we set __Uptime to the difference in seconds between when the current row's failure completed (RepairCompleted) and the end of our data, which we have set to December 31, 2013. Otherwise, if __Next is not blank, we compute __Uptime by taking the difference in seconds between the current row's RepairCompleted column and the computed subsequent failure event for the row, __Next.
Now we can compute the total uptime for all rows within the context of __TotalUptime. To do this, we simply need to use SUMX to sum our __Uptime column across all of the rows in the table variable, __Table1.
Now that we have the total uptime for all machines, we need to determine the maximum available time for the machines to operate. For our recipe, we have set our data start and end dates to January 1, 2010 and December 31, 2013, respectively. We also assume a 24/7 operation with no breaks. Thus, to get the maximum availability, __MaxAvailable, we compute the number of days in our dataset, __Days. Then, we can simply multiply __Days by 24 * 60 * 60, the number of seconds in a day. If we did include time for breaks or shift changes, we would deduct the amount of time for those breaks and shift changes from our __MaxAvailable value since the machines are not expected to be in operation during those times.
Now we can return the final value for Availability by dividing our total uptime, __TotalUptime, by the maximum availability, __MaxAvailability.
The Performance measure deals with data in the R04_Production table and is much simpler to calculate than Availability. For the Performance measure, we simply need to divide the sum of the actual parts produced, Actual, and the sum of the maximum production capacity, Capacity.
The Quality measure is nearly identical to the Performance measure, except that we are dividing the sum of the good (non-defective) parts produced, Good, and the sum of the actual parts produced, Actual.
Finally, for the OEE measure, we simply need to multiply our three measures together, that is, Availability, Performance, and Quality.