Detecting time "off"

[Anonymous]

I have a dataset that comprises the start and end times of recurring events. The events are not linked in the data, and each appear as a row, only categorised by whether they're a start or end event and their "Product Name".

I want to calculate the amount of time a day that there is no event and then show this as a percentage of total time.

My approach would be to associate each End event with the most recent (prior) start event, by product type.

i.e. Have a column of all start events, with a 2nd calc column that asks, what's the next closest date that has an End event and is the same product type.

Any input on the best approach to achieve this would be much appreciated.

 

[Kristjan76]

Hi,

In order to being able to help you, you should provide the data, then it is possible to copy the data into Excel/Power BI and come op with some solution.

Is there  any point in time when you know that there is no event running, i.e. starting point. You could create a calculated column that counts number of start events before current time - number of stop events.

This is maybe a start for you, this will only work if you have the first start event, and then you might need to add some logic if you want to caculate for each day. And then there is a question what happens when Start and Stop event ocour on the same time?

Active connection = 
VAR t = Table4[Timestamp]

VAR starts = 
COUNTROWS(
    FILTER(
        Table4;
        Table4[Timestamp] <= t && Table4[Event] = "Start"
    )
) 

VAR stops = 
COUNTROWS(
    FILTER(
        Table4;
        Table4[Timestamp] <= t && Table4[Event] = "Stop"
    )
)

RETURN
starts-stops

Inactive sec. = 
IF(
    Table4[Active connection] = 0;
    VAR curTime = Table4[Timestamp]
    VAR nextStart = 
        MINX(
            FILTER(
                Table4;
                Table4[Event] = "Start" && Table4[Timestamp] > curTime
            );
            Table4[Timestamp]
        )
    RETURN
    IF(NOT(ISBLANK(nextStart));DATEDIFF(curTime;nextStart;SECOND))
)

[Anonymous]

@Kristjan76 thanks for your input, that appears to give a figure, but really I need the underlying blocks that generate that figure. I've updated my request, as it would actually be useful to generate a table making sense of the events before I try and calculate the time. Any thoughts on this?

What's the best way to share excel data on this forum?

[Kristjan76]

You can share the file via onedrive or dropbox link. Are you having trouble with importing the data from the excel file?

[Kristjan76]

I don't think so, people seem to be sharing via onedrive or dropbox

[Anonymous]

Just wondered if there was a mechanism to share data on the forum itself. Thanks