DAX Formula for Row-by-Row Difference Calculation

Objective

To calculate the difference between each row and the previous row within a data table in DAX.

DAX Formula

Below is a sample DAX formula that calculates the difference for a column named Value in a table named DataTable.

RowDifference = 
VAR CurrentValue = SUM(DataTable[Value])
VAR PreviousValue = 
    CALCULATE(
        SUM(DataTable[Value]),
        PREVIOUSROW(DataTable[Date])  // Assuming 'Date' is the index for row comparison
    )
RETURN
    CurrentValue - PreviousValue

Formula Explanation

1. Variables Definition:

   • CurrentValue: This variable holds the sum of the current row's Value. Adjust the aggregation method if necessary (e.g., using AVERAGE, MAX, or MIN based on your dataset).
   • PreviousValue: This uses the CALCULATE function in conjunction with PREVIOUSROW (assuming it's a column available for row order, such as a date or sequence number). It fetches the value from the previous row.

2. Return Statement:

   • The formula returns the difference between the CurrentValue and the PreviousValue.

Practical Example

Sample Data

Calculation Process

• Row for 2023-01-01: No previous row, so the output is NULL.
• Row for 2023-01-02: 150 - 100 = 50
• Row for 2023-01-03: 120 - 150 = -30
• Row for 2023-01-04: 130 - 120 = 10

Resulting Output

Conclusion

This DAX formula successfully computes the row-by-row difference. It is essential to ensure you have a unique ordering column (e.g., Date) to use with functions like PREVIOUSROW. For further learning on DAX and data manipulation, consider exploring resources available through Enterprise DNA Platform.