Calculating Machine uptime - is this possible in Power BI from the data available?

[RichardJ]

I was wondering if anyone could advise whether the following is possible in Power BI?

The table shows the available data:

Available Raw Data

The objective is to

1) Calculate the 'Active' time of the machine
2) Calculate the 'Inactive' time of the machine
3) For each row, add a calculated column showing the duration of the row (Elapsed Time)
4) For each row, add a calculated column stating whether the machine was 'Active' or 'Inactive' (Activity)

The machine starts becoming 'Active' when 'Status'="Stib" and 'Function'="ON"
The machine stops being 'Active' when 'Status'="Stib" and 'Function'="OFF"

I'd think I'd need the following calculated columns

Activity = Active or Inactive
Where Active = 'Status'="Stib" and 'Function'="ON" in the current row or can find 'Status'="Stib" and 'Function'="ON" in the first available previous rows (before finding STIB OFF)

Where Inactive = 'Status'="Stib" and 'Function'="OFF" in the current row or can find 'Status'="Stib" and 'Function'="OFF" in the first available previous row (before finding STIB ON)

Elapsed Time = duration in seconds from the time stated in the previous row

Hope the above explanation makes sense.

The element which is confusing me is how to calculate the time difference between the rows - especially if there are 'info' rows between the ON and OFF statuses.

If this is possible, or if there is a better way of achieving the answer then any pointers would be appreciated.

Thank you!

[JarroVGIT · Accepted solution]

Cool question! I created a mockup of your data set (it's better to copy it into your question next time, rather than a screenshot, so we can replicate faster). I've loaded this into Power BI. The first thing I did was filter out irrelevant information (in the Query Editor), such that the Function column only shows ON or OFF. I then added an Index column, so my table looks like this when I start making my report:

Table we have prepared.

Now, we need to lookup the next Time value with a change of Function. For clearity, we add it as a calculated column now. The formula is:

NextStatusChangeTime = CALCULATE(MIN(Table1[Time]), FILTER(Table1, Table1[Function] <> EARLIER(Table1[Function]) && Table1[Time] > EARLIER(Table1[Time])))

This looks at the table, applies a filter on it (by looking for a status other then current row Status,  and to look for  Time later then the current row  Time.  This result in the following table:

Our dataset, with an additional column.

Next up is calculating the TimeDiff. We do this with the following calculated column:

TimeDiff = DATEDIFF(Table1[Time], Table1[NextStatusChangeTime], SECOND)

Ofcourse you can change the time unit to whatever you want it to be, for example MINUTE. This results in the following table:

Final table, with TimeDiff column.

With this, you can calculate the total amount of active time, by creating a measure that sums the column TimeDiff where Function is ON:

TotalActiveTime = CALCULATE(SUM(Table1[TimeDiff]), FILTER(ALL(Table1), Table1[Function] = "ON"))

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[RichardJ]

Thank you for the fabulous response @JarroVGIT .

Makes perfect sense.

Appreciate you taking the time and effort to explain