Often in a Power BI visual one wants to get the value from the previous row to use in a calculation in the current row (e.g., to see if there’s a change between the previous value and the current value). The Previous Row Value DAX pattern can be of help when faced with this issue.
As with most situations in Power BI, there are many ways to solve the same problem: here are a couple:
Method 1: Add DAX Index Column
1-Add a [DAX Index] calculated column to your table
DAX Index =
// DAX PATTERN NAME: Previous Row Value - Method 1 - Step 1
// NOTES: Order the values (using RANKX) in a calculated column, then use this in another calculated column to return the previous value
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename ALL occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
RANKX(
ALL( Quantities ), -- replace [Quantities] with the table containing your values
Quantities[Date],, -- replace Quantities[Date] with the name of the field that contains the unique values on which to order (rank) the values
ASC,
Dense
)
NOTE: if there is already an appropriate Index available in your dataset, or if one was added in Power Query when transforming the data, then the above [DAX Index] column is not necessary; the formula below can then be adjusted accordingly. (Such an [PQ Index] column has been added in the attached example PBIX.)
2-Add a [Previous Value] calculated column to your table
Previous Quantity =
// DAX PATTERN NAME: Previous Row Value - Method 1 - Step 2
// NOTES: Use the [DAX Index] calculated column created in [Method 1 - Step 1] to return the previous value
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _CurrentRowIndex = Quantities[DAX Index] -- replace [Quantities] with the table containing your values
VAR _PreviousRowIndex = CALCULATE(
MAX( Quantities[DAX Index] ), -- replace [Quantities] with the table containing your values
FILTER( Quantities, Quantities[DAX Index] < _CurrentRowIndex ), -- replace [Quantities] with the table containing your values
ALL( Quantities[DAX Index] ) -- replace [Quantities] with the table containing your values
)
VAR _Result = CALCULATE(
MAX( Quantities[Current Quantity] ), -- replace Quantities[Current Quantity] with the column containing the current value of interest
FILTER( Quantities, Quantities[DAX Index] = _PreviousRowIndex ) -- replace [Quantities] with the table containing your values
)
RETURN
_Result
3-Use the [Current Value] and [Previous Value] in your visual, and include as desired in additional calculations
Method 2: DAX Calculation using DATEADD or TOPN
When there is a continuous series of data to order by (most often dates), a simple use of CALCULATE with DATEADD will product the correct value.
Sales Previous Row (Continuous Dates) =
CALCULATE( [Total Sales], DATEADD( Dates[Date], -1, DAY ) )
This method, however, does not produce the correct value when the series of data to order by is discontinuous.
One can account for this by instead using TOPN to get the most recent value.
Sales Previous Row (Discontinuous Dates) =
// DAX PATTERN NAME: Previous Row Value - Method 2 - Measure
// NOTES: Use the TOPN construct with a measure to return the previous value
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
[Total Sales], -- replace [Total Sales] with the measure to be calculated
TOPN(
1,
FILTER(
ALLSELECTED( Sales ), -- replace [Sales] with the table containing your values
Sales[Order Date] < MAX( Dates[Date] ) -- replace Sales[Order Date] with the column containing the value date; replace Dates[Date] with the date value of your Dates table
),
Sales[Order Date], -- replace Sales[Order Date] with the column containing the value date
DESC
)
)
This same construct can be used with column values as well as measures:
Value Previous Row (Discontinuous Dates) =
// DAX PATTERN NAME: Previous Row Value - Method 2 - Column
// NOTES: Use the TOPN construct with a column to return the previous value
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
MAX( 'Values'[Value] ), -- replace 'Values'[Value] with the column containing the value of interest
TOPN(
1,
FILTER(
ALLSELECTED( 'Values' ), -- replace ['Values'] with the table containing your values
'Values'[Value Date] < MAX( 'Values'[Value Date] ) -- replace 'Values'[Value Date] with the column containing the value date
),
'Values'[Value Date], -- replace 'Values'[Value Date] with the column containing the value date
DESC
)
)
An alternate (and simpler) coding technique that can be used whenever a field in ascending order is available in the fact table is to use variables to identify the previous record, e.g.,
Sales Previous Row (Discontinuous Dates) V2 =
// DAX PATTERN NAME: Previous Row Value - Method 2 - Measure V2
// NOTES: Use variables to get the previous fact table date, then filter for that date
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
VAR _CurrentDate =
SELECTEDVALUE( Sales[Order Date] ) -- replace Sales[Order Date] with the column containing the value date
VAR _PreviousDate =
CALCULATE( MAX( Sales[Order Date] ), -- replace Sales[Order Date] with the column containing the value date
FILTER(
ALLSELECTED( Sales ), -- replace [Sales] with the table containing your values
Sales[Order Date] < _CurrentDate ) ) -- replace Sales[Order Date] with the column containing the value date
VAR _Result =
CALCULATE( [Total Sales],
FILTER(
ALLSELECTED( Sales ), -- replace [Sales] with the table containing your values
Sales[Order Date] = _PreviousDate -- replace Sales[Order Date] with the column containing the value date
)
)
RETURN
_Result
_NOTE: If you are using the Direct Query data connection mode, many of these time intelligence DAX functions are unavailable (refer to https://docs.microsoft.com/en-us/power-bi/connect-data/service-dataset-modes-understand for details.) In this case the alternate pattern [Sales Previous Row (Discontinuous Dates) V2] described above can be used. A forum example of dealing with previous row value in Direct Query mode in this way may be found at https://forum.enterprisedna.co/t/dax-row-minus-previous-row/9289/6._
_NOTE: There are also many other references available that describe the use of the EARLIER function, which was originally necessary in DAX due the absence of variables. Once variables became available in DAX, the use of EARLIER was no longer recommended, and, as such, it will not be discussed further here. More information can be found at https://docs.microsoft.com/en-us/dax/earlier-function-dax._
DAX Patterns - Previous Row Value.pbix (395.9 KB)
I’ll have a deep look at the writing you did to understand for future purposes.
- Let’s do it! DAX v. PQ showdown! Though we know how those usually end for me…
) it is weird because you placed a “Company” slicer on top of another duplicate of “Company” slicer. Then I noticed you only filtered with 1 company:
! Btw I never doubt of you 