OFFSET – value from previous, next… row in visual (DAX – Power Pivot, Power BI)

OFFSET is a function, that in Power BI shows the value from another row (previsou, next etc.)

Like here, it shows the previous value:

OFFSET is used in a CALCULATE function, it can have two or three arguments:

  • Previous sumo of Price = CALCULATE(
        SUM(Sheet1[Price]),
        OFFSET(
            -1,
            ALLSELECTED(Sheet1[Color])))
Because:
  • -1 defines the “movement”. For example -1 means previous row, +2 means two rows after etc.
  • ALLSELECTED is used for column we are working with
  • The last, voluntary, argument can contain the ORDERBY function. It defines the direction used for counting (ASC or DESC)