Finding the difference between the time from first and last occurrence

[xsaldana]

Hello! I am trying to find the difference in unixtime from when a sensor was first registered to when it was registered again. I color coated the chart to empashize the sensor type. The "Time since last reading" column is the one that I am attempting to create and the one to the right of it has the equations. I am having issue because the pH sensor has 3 readings at different depths, but I only take the difference when the three readings appear again. Opposingly, I only recieve one readign for the ambient temp, water temp, and oxygen level. Any suggestions?

[v-kkf-msft · Accepted solution]

Hi @xsaldana ,

Please try the following formula:

Column = 
VAR _current = Data[unix time]
VAR _previous =
    MAXX (
        FILTER (
            Data,
            Data[sensor] = EARLIER ( Data[sensor] )
                && Data[Depth (m)] = EARLIER ( Data[Depth (m)] )
                && Data[unix time] < EARLIER ( Data[unix time] )
        ),
        Data[unix time]
    )
RETURN
    IF ( ISBLANK ( _previous ), BLANK (), _current - _previous )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@xsaldana Seems like your > sign should be a < sign. And, you could use your Index potentially instead of unix time value for finding previous. Also, not sure about that last ). How many rows are there? Also, you *might* get better performance from this variation:

Column = 
var _sensor = Data[sensor]
var _current = Data[unix time]
var _previous = MAXX(FILTER(Data,
                       Data[sensor] = _sensor &&
                       Data[unix time] < _current
               ),Data[unix time])
return 
_current - _previous

[Greg_Deckler]

@xsaldana Seems like you want something like MTBF: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

Also, this might be handy: Unix2UTC and UTC2Unix - Microsoft Power BI Community

[xsaldana]

@Greg_Deckler Thank you! unfortunately, your suggestion didn't work for me. It freezes powerbi. 

Below is what I did. Any suggestions? Additionally, my time needs to stay in unix.

Column = 

var _current = Data[unix time]

var _previous = MAXX(FILTER(Data,

                       Data[sensor]=EARLIER(Data[sensor]) &&

                       Data[unix time]>EARLIER(Data[unix time])

               ),Data[unix time])

return 

_current - _previous )