Power BI — Offset Function
The OFFSET function retrieves a result based on the relative positioning of the data
The offset function returns a single row that is positioned either before or after the current row, within the specified partition, sorted in the specified order, based on the offset provided. If the current row cannot be deduced to a single row for whatever reason, multiple rows will be returned.
Instead of being pushed to the data source, DAX functions like Offset are executed within the DAX engine. These DAX functions have shown significantly improved performance compared to existing DAX expressions, particularly when sorting non-continuous columns is necessary.
OFFSET ( <delta/Number>, <relation/Table expression>, <order By from relation>, <blanks>, <partition By relation> )
Model: I am using the standard sales model, which I am using for all my videos and blogs. Sales fact with a key measure [net], joined with dimensions: Item, Geography, Date, and Customer.
Let us create a visual using the item category and net. We will also create our first offset measure Last Category and add to it.
Measure used. Also adding the next category
Last Category = CALCULATE([net], OFFSET(-1, ALLSELECTED(‘Item’[Category]), ORDERBY(‘Item’[Category],asc)))
Next Category = CALCULATE([net], OFFSET(1, ALLSELECTED('Item'[Category]), ORDERBY('Item'[Category],asc)))
Once you add the brand to a visual, this measure will not work as expected as it is still looking for the order based on category
We will need a new set of measures
Last Category Brand = CALCULATE([net], OFFSET(-1, ALLSELECTED('Item'[Brand],'Item'[Category]), ORDERBY('Item'[Category],asc),KEEP,PARTITIONBY('Item'[Brand])))
Next Category Brand = CALCULATE([net], OFFSET(1, ALLSELECTED('Item'[Brand],'Item'[Category]), ORDERBY('Item'[Category],asc),KEEP,PARTITIONBY('Item'[Brand])))
This is how we can get time intelligence. The default sort column will not work. We have to use sort column in relation and order by
Offset Previous Month = CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year]),ORDERBY('Date'[Month Year sort])))
In case we want the previous month in the same year
Previous Month in Year = CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]),KEEP,PARTITIONBY('Date'[Year])))
One can create a combined measure for previous values like
Previouse Value = SWITCH(TRUE(),
ISINSCOPE('Date'[Date]), CALCULATE([Net], OFFSET(-1, ALL('Date'[Date]))),
ISINSCOPE('Date'[Month Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]))),
ISINSCOPE('Date'[Qtr Year]),CALCULATE([Net], OFFSET(-1, ALL('Date'[Year], 'Date'[Qtr Year]))),
ISINSCOPE('Date'[Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Year])))
)
You can find the video below
Free Microsoft Power BI Course for Beginners, 2023
My Medium blogs can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
