Time difference between rows

[Anonymous]

Hi

I've been trying to solve my problem for a few days, but even with research on the forum, I can't seem to make it work. 

Concept :

I get pings from 7 machines at varying delays. These looks like this :

I am trying to calculate the availability of these machines in the last days. To date, I created a pie-chart that looked at the number of ping occurrences for each of the machine statuses (0-2-3-4-5-6, meaning stopped, running, in alarm, etc). The problem is that if the machine pings status "6" 60 times (every minute) for an hour, then pings status "2" once and then goes an hour without a ping, the pie-chart will show that status "6 " takes up 60 times more space, when it should be equal.
So I need to calculate the difference (in seconds) of time between two lines, per machine, since the last ping. Like this : 

I tried a couple of measure and calculated column, and the closest I got is this :

TimedifferenceMeasure = 
VAR CurrentIndex =
    MAX ( machine_status[id] )
VAR PreviousIndex =
    CALCULATE (
        MAX ( machine_status[id] ),
        FILTER ( machine_status, machine_status[id] < CurrentIndex )
    )
VAR difference =
    DATEDIFF (
        CALCULATE (
            MAX ( machine_status[Creation Time] ),
            FILTER ( ALL ( machine_status ), machine_status[id] = PreviousIndex )
        ),
        CALCULATE (
            MAX ( machine_status[Creation Time] ),
            FILTER ( ALL ( machine_status ), machine_status[id] = CurrentIndex )
        ),
        SECOND
    )
RETURN
    IF ( difference > 43200, 43200, difference )

(The purpose of the 43200 at the end is to limit the time per status to 12 hours. Skip that, the machine is usually closed)

But it doesn't give me what I want. It seems to calculated only the last occurrence, meaning that frequents status get under-represented.

Here's the link to my file :

https://umanomed-my.sharepoint.com/:u:/g/personal/anthony_laroche_umanomedical_com/EdvcvOQ7WWRMsPGb5...

Thank you !

[Anonymous]

Thanks tackytechtom ! 

I ended up doing it in Power Query like in your link. A couple changes to adapt to my situation and it works now ! 

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Does this one here help you?

Ranking Column in your case should be the [Created On] column, where as the Grouping Column is [id]

DateDiffColumnWithoutIndex = 
DATEDIFF (
   'TableName'[RankingColumn],
    CALCULATE (
        MIN ( 'TableName'[RankingColumn] ),
        FILTER ( 'TableName', 'TableName'[RankingColumn]  > EARLIER ( 'TableName'[RankingColumn]  ) ),
        FILTER ( 'TableName', 'TableName'[GroupingColumn] = EARLIER ( 'TableName'[GroupingColumn] ) )
    ),
    SECOND
)

Doing this in a calculated column might take quite some resources and time. Maybe consider adding this column in Power Query instead. This one here, might help you in that case.

Let me know if this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/