topic Time difference between rows in DAX Commands and Tips

Time difference between rows

Anonymous — Mon, 17 Oct 2022 14:18:46 GMT

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/EdvcvOQ7WWRMsPGb5iXmEj8BIf7WOpwn-PVyJOs1BZDE4Q?e=7Q2TyT

Thank you !

Re: Time difference between rows

tackytechtom — Mon, 17 Oct 2022 20:37:51 GMT

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/

Re: Time difference between rows

Anonymous — Wed, 19 Oct 2022 15:33:07 GMT

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 !