OFFSET
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
Syntax
The syntax of the OFFSET function is as follows:
OFFSET(reference, rows, columns, [height], [width])
Where:
– `reference` is the cell or range of cells that is used as a starting point for the offset calculation.
– `rows` is the number of rows by which to offset the reference. A positive number means that the offset will be below the reference, and a negative number means that it will be above the reference.
– `columns` is the number of columns by which to offset the reference. A positive number means that the offset will be to the right of the reference, and a negative number means that it will be to the left of the reference.
– `height` (optional) is the number of rows to include in the range that is returned by the function. If this argument is omitted, the range will include all rows from the offset position to the end of the table.
– `width` (optional) is the number of columns to include in the range that is returned by the function. If this argument is omitted, the range will include all columns from the offset position to the end of the table.
Example
Here’s an example of how to use the OFFSET function in Power BI DAX:
Suppose you have a table of sales data that looks like this:
| Date | Product | Sales |
|————|———|——-|
| 2022-01-01 | A | 100 |
| 2022-01-02 | B | 200 |
| 2022-01-03 | C | 300 |
| 2022-01-04 | D | 400 |
| 2022-01-05 | E | 500 |
To get the sales data for the last three days, you can use the following formula:
=OFFSET(B2, COUNTA(B:B)-4, 0, 3, 2)
This formula uses cell B2 as the reference point, and then offsets it by the number of rows equal to the count of non-empty cells in column B minus 4, which gives us the last three rows of the table. We also specify a range of 3 rows and 2 columns to include both the Product and Sales columns in the range.
The resulting range would be:
| Product | Sales |
|———|——-|
| C | 300 |
| D | 400 |
| E | 500 |
The OFFSET function in Power BI DAX is a valuable tool that enables you to manipulate ranges of cells and values with ease. By understanding its syntax and usage, you can create dynamic ranges that adapt to changing conditions, and calculate values based on specific ranges within your data.
