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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pdupreez
Frequent Visitor

Time Difference over 24h periods other than midnight to midnight

I work in an Industrial environment, and a key KPI is plant reliability, availability, MTBF (Mean Time Between Failure) etc. This implies recording all stoppages by Time Stopped and Time Started. Obviously, as a piece of equipment has no sense of time, they will break across days, making it a bit difficult to keep track of my stoppage times per day. This information is automatically extracted from the control PLC's (Programmable Logic Controller)

 

Also, the time period over which a 24h period runs is not from midnight to midnight, but from 07h00 (am) to 07h00 (am)

 

Example: Unit one stops at 04h00 till 09h00 on the 20/04/2016. For the calculation to work, I need 04h00 till 07h00 to be captured on the 19/04/2016, and the time from 07h00 till 09h00 to be captured on 20/04/2016. There may be multiple stops in a 24h period.

 

Running hours is calculated as 24 - Sum(LostTime) on a specific date. In the above example it will be 24-3, i.e. 21 hours for the 19th, and 24-2=22h on the 20th

 

Reliability will then be calculated as (24-Sum(LostTime))/24 as a percentage. i.e.  21/24 = 87.5% on the 19th and 22/24 = 91.7% on the 20th

 

Total hours (run hours + lost hours) cannot be more than 24. and using straight forward DateDiff(StopDate, StartDate, hours) will not work as a stop can also be over a period longer than 24h ie the unit stops on Sunday and restart on Wednesday

 

The data is simple enough:

 

Unit               StopDate/Time           StartDate/Time             TypeOfStoppage

Unit 1             2016/04/20 04:00:00    2016/04/20 09:00:00       Breakdown

Unit 1             2016/04/20 16:00:00    2016/04/20 19:00:00       Planned Stoppage

etc, etc

2 REPLIES 2
Greg_Deckler
Super User
Super User

Thanks for providing a great explanation and raw data. Hopefully this will help you get to your solution. Using your data, you can create the following columns:

 

DayStart = DATEVALUE([StopDate/Time]) + TIME(7,0,0)

RawHoursBefore = (HOUR(StopTimes[DayStart])-HOUR(StopTimes[StopDate/Time]))

HoursBefore = IF(StopTimes[RawHoursBefore]>0,[RawHoursBefore],0)

HoursAfter = IF(StopTimes[StopDate/Time]<StopTimes[DayStart],HOUR(StopTimes[StartDate/Time])-HOUR([DayStart]),HOUR(StopTimes[StartDate/Time]) - HOUR(StopTimes[StopDate/Time]))

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks a lot for the support. I will run it in the morning when I get back at the office, but I am a bit unsure of how I will use the results.

 

I assume from this I will get something like:

 

Unit     StopDate/Time           StartDate/Time   TypeOfStop         DayStart         RawHoursBefore   HoursBefore   HoursAfter

Unit 1   20160420 04:00:00    20160420 09:00:00   Breakdown   20160420 07:00:00          3                              3                       2

 

This provide the immediate answer to what was before the cuttoff time of 07h00, and after 07h00, but what I now need to be able to sum the LostTime data for records before, i.e. with a StopDate/Time of 20160419 and that with a StopDate/Time of 20160420 AND an HoursBefore value > 0.

 

I am not sure if I explain it clearly enough, but the sum of all hours that fell between 07h00 on the 19th and 07h00 on the 20th becomes the lost time for the 19th, and that will then be used in measures/kpi's such as NAI, GAI, MTBF, MTBS, OEE etc (Net Availability Index, Gross Availability Index, Mean Time Between Failure, Mean Time Between Stops, Overall Equipment Efficiency) by using the Type Field.

 

The TYPES are essentially INCIDENTS, PLANNED, CIRCUMSTANTIAL

 

In Excel, I do it by creating a new table (per day) using SUMIFS to create a record per day that provides me with a table that looks as in the following example:

 

Unit        Date              Incident  Circumstantial  Planned    NoOfStops NoOfFailures   RunTime

Unit 1     19/04/2016      3             2                         1                 1                  2                       =24-sum(Inc, Circ, Plan) = 18

 

From this table I can create all the measures/kpi's mentioned above with the aid of an additional table Production providing the production per Unit per day, i.e.

 

Unit         Date              TonsProduced

Unit1        19/04/2016    1200

 

This in turn is related to a Unit Capacity Table, providing the design capacity of each unit, to allow calculation of Capacity Utilisation factor, which is used in OEE calculations (see OEE Calculation Method)

 

Unit         Capacity

Unit1        1500

 

At the end I may end up with a Table that gives me:

 

Date          Unit       Parameter Value

19042016  Unit1     GOEE        98%

19042016  Unit1     NOEE        98%

19042016  Unit1     GAI           99%

19042016  Unit1     NAI           99%

19042016  Unit1     PF             99%

19042016  Unit1     MTBS        7

19042016 Unit1      MTBF        12

etc etc

 

I am not sure if the above table is better with the parameters as FIELDS instead of each as a record, but I foresee the above structure gives me flexibility to add more parameters without changing the table structure.

 

There is an added complexity in that should I create the table above instead of using MEASURES, that I will get errors in doing MTD, YTD etc type aggregation, and it may be better to just use the first table in this post with MEASURES for the KPI's

 

Regards

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.