Forum Discussion

xsaldana's avatar
xsaldana
Regular Visitor
4 years ago
Solved

Finding the difference between the time from first and last occurrence

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?

 

 

 

 

Commands and Tips
DAX
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

4 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

    • xsaldana's avatar
      xsaldana
      Regular Visitor
      4 years ago

      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 )