ABS & REL parameters in DAX Window function

The recently introduced Window 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.

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

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.

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

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.

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] )
    )
)

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.

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] )
    )
)

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.