Previous Row Value

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)

7 Likes

Just adding some keywords to make this pattern easier to locate via forum search:
DAX pattern, previous row, previous date, RANKX, index, DATEADD, TOPN.

Related Content:

Below are a few examples of issues related to the Previous Row Value DAX Pattern from the eDNA resources.

Enterprise DNA Courses:

Enterprise DNA Forum (Search):

Enterprise DNA TV (YouTube):

2 Likes

Hi there,

Thanks for the supporting information, it is very useful!
Nevertheless, I am facing a situation where I do not find a solution “yet”…

I have a list of companies selling fuel materials where unit price value can be expressed either in cents per liter or cents per gallon and for each day there is a selling price which can be repeated if there has been no change for a certain period of time.

Data has been unpivoted this way:

image

I wish to be able to retrieve the previous different price on a row level. In many cases, it is not equal to previous day (or previous row value) because some companies may maintain their selling price during several days.

I first tried to work on retrieve previous row value and tried the option you listed above:

  • Method 1: DAX Index calc column + Previous value calc column, is not working. I guess having repeated rows for each day is messing with the Index logic and so returns incorrect previous row value.
  • Method 2: TOPN or DATEADD. TOPN would work if I would filter for only one company but as soon as many companies are filtered it return incorrect previous value. DATEADD seems to work with both filtering options (one or many companies).
  • Method Sales Previous Row (Discontinuous Dates): would work if I would filter for only one company but as soon as many companies are filtered it return incorrect previous value.

So previous row value looks good with DATEADD but still the final objective would be to retrieve previous different value (previous different selling price) and I cannot figure out how to write DAX to solve this.

In Power Query, I would not be able to work with Index column due to the repeated same date and so cannot expect to do the same than:

The example commented in the topic Counting how many customers have changed status - #5 by lfPBI was integrating a proper index column which facilitates the calculation… different than my context.

I would love to hear any further suggestion. Thanks.

Alexandre

Demo - Prices.pbix (91.9 KB) Demo - Prices.xlsx (433.2 KB)

Hi @Alex7891,

Can you let me know if this is what you are after, thanks!

image

I’ve created a new table called: Price (PrevValue)

  1. Grouped by; Company, Material, Unit
  2. Sorted the nested table ascending
  3. Added an Index
  4. Check for price change on the previous row and fill down

Here’s your sample data: eDNA - Previous Price.pbix (132.6 KB)
Note that if you change the FileLocation parameter all queries will be restored

I hope this is helpful.

1 Like

@Alex7891,

Really interesting twist on the previous value pattern. I’m close to a solution on this. Will work on it at lunch and have something to you later today.

  • Brian

@Alex7891,

I see @Melissa’s already got you covered. I’ll finish my solution later today, just to provide another approach for learning purposes, since I took the DAX road in my solution.

  • Brian

Great @Melissa, you got it perfectly done ! Many thanks !!!
You’re a M code sorceress :grin: I’ll have a deep look at the writing you did to understand for future purposes.

Thanks @BrianJ. I’d be curious to see a DAX solution for a data listed this way… All I’ve seen on the web was about previous row value and not about previous change value (including previous row and/or several previous rows when price has been same for few days).

Appreciate your efforts. Thanks

That sounds like a video request to me :grinning:
Just made a note… hope you’re subscribed to the Enterprise DNA YouTube channel !

Sure I am :slight_smile:

@Melissa,

:+1: - Let’s do it! DAX v. PQ showdown! Though we know how those usually end for me…

1A738572-F89E-4FE3-A6EA-3C87452F27BD

  • Brian
2 Likes

@Alex7891,

Here’s the DAX solution version that produces identical results to Melissa’s PQ-based solution. It’s actually not a dramatic variation from the standard previous value pattern, just adding a second filter condition in the previous index variable.

Here’s the measure (formatted in DAX Cleanup, short-line style, despite the fact that all the cool kids these days seem to be using long-line style…):

Prev Diff Price =
 
VAR CurrPQIndex = SELECTEDVALUE( 'Price'[PQIndex] ) 
VAR CurrValue = SELECTEDVALUE( 'Price'[Value] ) 

VAR PrevIndex = 
CALCULATE(
    MAX( 'Price'[PQIndex] ),
    FILTER(
        ALLSELECTED( 'Price' ),
      'Price'[PQIndex] < CurrPQIndex &&
     'Price'[Value] <> CurrValue
    )
) 

VAR PrevPrice = 
CALCULATE(
    MAX( 'Price'[Value] ),
    FILTER(
        ALLSELECTED( 'Price' ),
      'Price'[PQIndex] = PrevIndex
    )
) 

RETURN
PrevPrice  

I hope this is helpful. Full solution file attached below.

– Brian

eDNA Forum Demo - Previous Different Prices Solution.pbix (268.5 KB)

3 Likes

Hi @BrianJ,

Thanks for your reply and your efforts :slight_smile:
(But :neutral_face:) 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:
image

Again if you keep just one company filtered it works fine (thank you :slight_smile: ) but if you filter with multiple companies there’s still the issue. It will find the previous row but not from the same company and the [PQIndex] seems not to be useful at the end… tricky tricky ?! :confused:
image

I guess data would rather be transformed in PQ, DAX seems complicated in this context…
Thanks again :wink:

Best,
Alexandre

@Alex7891,

Awww, c’mon - I’m not conceding that easily. :grinning:

Just have to change up the sort order and add one more filter condition to the Previous Index calculation for Company Name, and we’re back in business:

Prev Diff Price = 
VAR CurrPQIndex = SELECTEDVALUE( 'Price'[PQIndex] ) 
VAR CurrValue = SELECTEDVALUE( 'Price'[Value] ) 
VAR CurrCompany = SELECTEDVALUE( 'Price'[Company] )

VAR PrevIndex = 
CALCULATE(
    MAX( 'Price'[PQIndex] ),
    FILTER(
        ALLSELECTED( 'Price' ),
      'Price'[PQIndex] < CurrPQIndex &&
     'Price'[Value] <> CurrValue &&
     'Price'[Company] = CurrCompany
    )
) 

VAR PrevPrice = 
CALCULATE(
    MAX( 'Price'[Value] ),
    FILTER(
        ALLSELECTED( 'Price' ),
      'Price'[PQIndex] = PrevIndex
    )
) 

RETURN
PrevPrice 

Full solution file attached below.

  • Brian

eDNA Forum Demo - Previous Different Prices Solutionv2.pbix (267.4 KB)

3 Likes

Great @BrianJ, you made my awakening better today :smiley: ! Btw I never doubt of you :wink: :stuck_out_tongue:

Finally I can see that the [PQ Index] was worth it!
Thanks a lot for your efforts !

Alexandre

The solutions listed are predicated on the column been a numeric. What if it is a categorical instead that you are trying to find the last value of?

Hi @Cameron,

Please provide a sample, create a new topic and avoid asking new and/or follow up questions in an already closed thread. We ask, because those are easy to miss so there’s a great chance you’ll never receive a response…

However if you can determine the order of the categorical data this should be easy to solve.

All the best,
Melissa

Thanks Melissa, i will add a sample next time…before my question. or make a new topic.

1 Like