Forum Discussion

asundquist's avatar
asundquist
Frequent Visitor
3 years ago
Solved

Offset Function not reading previous row.

Maybe a person here can help me, because searching and support cannot.  The offset function seemed like what I wanted but I have not gotten it to work. I made a mockup sheet today to figure out if...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    asundquist You don't need OFFSET for this, you can use the MTBF pattern. 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, OFFSET gets wonky at times. It's certainly not easy or intuitive to use.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

asundquist You don't need OFFSET for this, you can use the MTBF pattern. 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, OFFSET gets wonky at times. It's certainly not easy or intuitive to use.

 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Greg_Deckler 
I sort of agree with you. I don't know how these functions really work and they don't produce consistent results when used within a filter context of a visual. Their behavior seem to be extremely difficult to understand. I prefer to do my things manually if you would say as I like to tell the engine what exactly I want it to do and how to do it rather that yhan relaying on a black box function that until now it doesn't seem that there is a way to verify what does it exactly do in different scenarios.