How to reference previous record. Need help on DAX

[JenniferWallace]

Hi I am trying to get the % change between consecutive records. 

Logic to determine the previous record is to sort the Pack Size in ascending order and then get the percentage change.

Help me to determine the previous record.

@123abc @talespin 

[Dangar332 · Accepted solution]

HI, @JenniferWallace 

try below column

Column = 
var a = MAXX(FILTER('Table','Table'[package size]<EARLIER('Table'[package size])),'Table'[package size])
var b = CALCULATE(SUM('Table'[dollar]),FILTER('Table','Table'[package size]=a))
RETURN
IF(b=BLANK(),BLANK(),('Table'[dollar]-b)/b)

and convert it to percantage using format pane

[123abc · Accepted solution]

To reference the previous record and calculate the percentage change between consecutive records in DAX (Data Analysis Expressions), you can use the following steps:

1. First, ensure that your data is properly sorted in ascending order based on the Pack Size.

2. You can use DAX functions like EARLIER() and LASTNONBLANK() to reference the previous record and calculate the percentage change.

Here's a sample DAX expression to calculate the percentage change:

Percentage Change =
VAR CurrentRecord = TableName[ColumnWithPackSize]
VAR PreviousRecord = CALCULATE(
LASTNONBLANK(TableName[ColumnWithPackSize], 1),
FILTER(ALL(TableName), TableName[ColumnWithPackSize] < CurrentRecord)
)
RETURN
IF(
ISBLANK(PreviousRecord),
BLANK(),
(CurrentRecord - PreviousRecord) / PreviousRecord
)

Replace TableName with the name of your table, and ColumnWithPackSize with the name of your column containing the pack sizes.

This expression calculates the percentage change between the current record and the previous record based on the sorted pack size. If there is no previous record (i.e., for the first record), it returns blank.

Make sure to adjust the column names and table names according to your data model. This formula should be added as a new calculated column or measure depending on where you want to use it in your Power BI report or DAX query.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Dangar332 · Accepted solution]

HI, @JenniferWallace 

try below column

Column = 
var a = MAXX(FILTER('Table','Table'[package size]<EARLIER('Table'[package size])),'Table'[package size])
var b = CALCULATE(SUM('Table'[dollar]),FILTER('Table','Table'[package size]=a))
RETURN
IF(b=BLANK(),BLANK(),('Table'[dollar]-b)/b)

and convert it to percantage using format pane