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.

 

asundquist's avatar
asundquist
Frequent Visitor
3 years ago

Greg_Deckler Thanks for the fast response! I have a follow up question:

Is there anyway to allow your solution to go the opposite way, or to be offset by more than one space, like I expected OFFSET to be capable of?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    asundquist Oh yeah, there's all kind of fancy stuff you can do without those functions. Most of it involves abusing CONCATENATEX and torturing the PATHITEM function. Like this:

    The Mythical DAX Index - Microsoft Power BI Community

     

    I believe the OFFSET, INDEX and WINDOW functions are pre-cursors and building blocks for visual level DAX calculations (which are a bad idea to begin with IMHO). They really only work under the very specific circumstances that are documented in the blog article for their release and there's just too many ways to break them right now in my opinion. If they really are the building blocks for DAX visual level calculations, those are going to be a nightmare.