# ABS & REL parameters in DAX Window function

The recently introduced [Window](https://learn.microsoft.com/en-us/dax/window-function-dax) function in DAX have made life easier when dealing with complex calculations especially that requires calculating running totals, ranking, and dynamic windowed aggregations across different contexts and partitions.

I am lot more intrigued with the `ABS` and `REL` arguments of the window function as it has provided a high level of flexibility with respect to running totals across window partitions. In this blog I will try to provide some insights on how to leverage these two arguments within the window function.

### Definition :

**ABS** :This tells the window to start from the first row (absolute reference from the beginning of the partition).  
**REL**: This tells the window to end 1 row before the current row (relative to the current row).

You can find the official definition [here](https://learn.microsoft.com/en-us/dax/window-function-dax).

Sounds simple right ? Maybe..so lets give it a try with the a few examples in the window function.

We have the following data set with columns *Region\_id, Region, Sale Date, Total Sales*

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1726592720210/81d12620-4232-46ec-a590-811b816bc295.png align="left")

With the new Window function calculating a running total has become a bit easier across window partitions. The partition for this data is on region.

```apache
Window_Fn =
CALCULATE (
    SUM ( Table[total_sales] ),
    WINDOW (
        0,
        ABS,
        1,
        REL,
        ALLEXCEPT ( Table, Table[region] ),
        ORDERBY ( Table[Date].[Date], ASC),
        PARTITIONBY ( Table[region_id] )
    )
)
```

The above measure `Window_Fn` with `0,ABS` and `1,REL` implies that for the current row the `sum(total_sales)` is calculated as the sum of all the sales in the previous rows plus the current row in the partition plus the total sales values on the immediate next row relative to the current row.

Lets focus on the output for North America and Europe

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1726594260661/d01e9bba-2266-4aab-8497-754ab92b7b0c.png align="left")

For North America for date the `1/1/2024` the calculated window value would be `1500+1550=3050`.This is because the `0th`(which defaults to `1`) absolute position of the partition is `1/1/2024` and the relative position which is next row relative to the current row of the partition is `1/5/2024`.Now this would repeat for each row till we reach a position in the partition where the `REL` value and the `ABS` value for a given current row are equal. This happens for row where the Sale Date is `1/20/2024` and `1/25/2024` for North America and `1/23/2024` and `1/28/2024` for Europe.

At row `1/20/2024` the relative row is `1/25/2024` and for `1/25/2024` for North America there is no relative row value , so the value of the measure will be sum of all the `total_sales` values in the partition for those 2 rows.

Lets change the `ABS` and `REL` values to `2` and `-1` respectively. In this case it tells the window to start at second row of the partition and for any given current row the partition ends one row prior to the current row.

```apache
Window_Fn =
CALCULATE (
    SUM ( Table[total_sales] ),
    WINDOW (
        2,
        ABS,
        -1,
        REL,
        ALLEXCEPT ( Table, Table[region] ),
        ORDERBY ( Table[Date].[Date], ASC),
        PARTITIONBY ( Table[region_id] )
    )
)
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1726599636215/10707637-e13e-4a8e-ba7a-cdce0434f6d7.png align="left")

The `ABS` value of the partition starts at `1/5/2024`. For the first row of the partition for North America the `ABS` and `REL` value wouldn’t add much meaning as there is no row prior to `1/1/2024` and the row that matches the `ABS` value of 2 is `1/5/2024`.To put it more into perspective, say for example if there's an interval with a start value of 10 and an end value of 2, it’s impractical to find a value of 3 within that range. This makes the proposition illogical or meaningless. Same would be the case for row `1/5/2024` for North America and `1/8/2024` for Europe. Remember the start value of the partition is `1/5/2024` for North America and the relative value for each current row should be equal or greater than the absolute value.

Lets move to the third row i.e. `1/10/2024` for North America. For this row the window starts at `1/5/2024` and ends at `1/5/2024` as the `ABS` value is `1/5/2024` and the relative value for the current row of `1/10/2024` is `1/5/2024`.

Lets perform the same checks for rows of Europe. The start date of the partition is `1/8/2024` and for the first row `1/3/2024` the relative value does not exist. Moving to the next row `1/8/2024` the start row would be `1/8/2024` and end row is `1/3/2024` which is meaningless. For the third row `1/13/2024` the start of the window is `1/8/2024` and end of the window is `1/8/2024`.

For the last row for Europe, the window partition starts at `1/8/2024` and ends at `1/23/2024`.Output of the function for this row would the sum of all values of Total Sales from date `1/8/2024` to `1/23/2024` i.e. `1250+1300+1350+1400 =5300`.

In the last two previous examples we saw the start position of the partition defined by `ABS` and the end partition by `REL`

Let us see in this example on how the summation is calculated where the start position of the partition is defined with `REL` and the end position by `ABS`.

```apache
Window_Fn = 
CALCULATE (
    SUM ( Table[total_sales] ),
    WINDOW (
        -2,
        rel,
        2,
        abs,
        ALLEXCEPT ( Table, Table[region] ),
        ORDERBY ( Table[Date].[Date], ASC),
        PARTITIONBY ( Table[region_id] )
    )
)
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1726614459339/eb4a80e5-3d0c-429c-babb-08e7b11ef1ce.png align="left")

The start row of the partition for North America for any given current row is two rows before the current row and the end row is the second row of the partition. So, for the first row in the partition the output for the window function is `1500`+`1550` as there are no rows prior to `1/1/2024`.For second row as well it would be the same `1550`+`1500` and same would be the case for third row. For the fourth row, as the argument is two rows relative, the start row would be `1/5/2024` which is the equal to the start row of the partition defined by `ABS` , thus the summation will be `1550` .The two rows relative for the current row of `1/15/2024` is `1/5/2024` and the absolute value is also is `1/5/2024` the output for this row of `1/15/2024` is `1550`.

The output of the measure for the fifth row will be empty as the start row for the partition is in between `1/10/2024` and `1/5/2024` which is meaningless. Same is the case for the sixth and final row `1/25/20204`.

### Conclusion

In conclusion, the introduction of Window functions in DAX has significantly streamlined complex data analysis tasks, offering enhanced flexibility for performing dynamic aggregations, comparisons, and calculations. A better understand on the `REL` and `ABS` functions used in Window function gives immense flexibility to perform row by row operations over a window partitions in DAX.