How to reference previous record. Need help on DAX

Pranali_R — Mon, 19 Feb 2024 15:52:15 GMT

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.

Re: How to reference previous record. Need help on DAX

Dangar332 — Tue, 20 Feb 2024 06:05:30 GMT

HI, Pranali_R

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

Re: How to reference previous record. Need help on DAX

123abc — Tue, 20 Feb 2024 05:37:10 GMT

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

First, ensure that your data is properly sorted in ascending order based on the Pack Size.
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.

topic How to reference previous record. Need help on DAX in DAX Commands and Tips

How to reference previous record. Need help on DAX

Re: How to reference previous record. Need help on DAX

Re: How to reference previous record. Need help on DAX