topic Re: Finding the difference between the time from first and last occurrence in DAX Commands and Tips

Finding the difference between the time from first and last occurrence

xsaldana — Tue, 10 May 2022 00:14:50 GMT

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?

Re: Finding the difference between the time from first and last occurrence

Greg_Deckler — Tue, 10 May 2022 00:31:39 GMT

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/339586.
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

Re: Finding the difference between the time from first and last occurrence

xsaldana — Tue, 10 May 2022 04:59:17 GMT

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 )

Re: Finding the difference between the time from first and last occurrence

Greg_Deckler — Tue, 10 May 2022 10:56:23 GMT

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

Re: Finding the difference between the time from first and last occurrence

v-kkf-msft — Thu, 12 May 2022 05:58:38 GMT

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.