Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
asundquist
Frequent Visitor

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 it was some sort or model connection I made on my actual work that was just breaking the function, but the problem persists. 

Here is what I have: 

asundquist_0-1683904673415.png

with the fourth column from this: 

 

DAX's idea of an "offset" = 

OFFSET(
    -1,
    ALL(Sheet1[Units]),
    ORDERBY(Sheet1[Lot])
    )

 


As you might notice, the offset performed on [Units] that results from this function isn't giving me the previous row as it appears in the table, it's giving me the next smallest value from the relation. I was under the impression that ORDERBY would allow me to force it to follow the order of the [Lot] column, but clearly I am mistaken. 

The third column is simply an example of what I want from a custom column, one where it does not abandon its relation to the other columns' order. 

Can anyone help?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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/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, OFFSET gets wonky at times. It's certainly not easy or intuitive to use.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@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/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, OFFSET gets wonky at times. It's certainly not easy or intuitive to use.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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?

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.