Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ilcaa72
Helper IV
Helper IV

Reference Previous Value in adjacent column

in Power BI Desktop... i have a Column that has prices, and I want to create a new column that shows a + sign or - sign, "if current value > previous value then "+", if current value < previous value then "-".  Its easy to do in Excel, how do I accomplish this in BI Desktop

 

thanks

 

IN EXCEL, B2 =IF(A1 >A2, "-","+")

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

I may be simplifying it, but DAX calculates the whole column at the same time, the visible order in the pivot is not really relevant for the calculation

Regardless - this should work, it looks up the Index that is lower than current index and where price is different than current price

=
VAR CurrentIndex = INTC[Index]
VAR CurrentPrice = INTC[Prices]
VAR PreviousIndex  = CALCULATE(MAX(INTC[Index]),FILTER(ALL(INTC),AND(INTC[Index]<CurrentIndex,INTC[Prices]<>CurrentPrice)))
VAR PreviousPrice = CALCULATE(SUM(INTC[Prices]),FILTER(ALL(INTC),INTC[Index]=MAX(PreviousIndex,1)))	//gets previous price
VAR Delta = INTC[Prices]-PreviousPrice	//calculates the delta
RETURN
IF(Delta<0,"-",IF(Delta>0,"+",BLANK()))






Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

8 REPLIES 8
TomMartens
Super User
Super User

Hey @ilcaa72

 

In comparison to using Excel the same needs a little more sophisticated approach in Power BI.

 

Here you will find a PBIX file

 

The approach here is to find a Value of the previous row,

Basically the pattern I use is to find the MAX Date of group of rows where all the dates in that group are smaller than the current date, I call that date previousTransactiondate. In my example it's a variable in the calculation for the measure "Previous Value".

Then I use the date I found to determine the previous value.

This is how the measure looks like:

Previous Value = 
var currentCustomer = FIRSTNONBLANK('Simple Facts'[Customer],1)
var currentTransactionDate = FIRSTNONBLANK('Simple Facts'[TransActionDate],1)
var previousTransactiondate = 
CALCULATE(
    MAX('Simple Facts'[TransActionDate])
    ,FILTER(ALL('Calendar'[Date])
        ,'Calendar'[Date] < currentTransactionDate
    )
)
var previousvalue = 
CALCULATE(
    SUM('Simple Facts'[Value])
    ,FILTER(ALLSELECTED('Simple Facts')
        ,'Simple Facts'[TransActionDate] = previousTransactiondate
        && 'Simple Facts'[Customer] = currentCustomer
    )
)
return
previousvalue

 

Please be aware that my example also contains an extra twist, the customer, this is maybe not necessary in your situation.
Also consider that a ROW index (a ROW identifier) maybe not be contiguous to applied slicer.
Nevertheless it's always a good idea to have a rownumber that uniquely identifies a row in each of your tables.


In my example file this unique identifier is the column value that is automatically created by the formula GENERATESERIES, but you do not need to consider this fact for your case 🙂 This is just due to an experiment to create sample data.

 

Hopefully this gives you an idea.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Zubair_Muhammad
Community Champion
Community Champion

Hi @ilcaa72

 

Try this.

 

First Add an Index Column from the Query Editor.

 

Then you can use this Column

 

Column =
VAR NextRow =
    CALCULATE (
        SUM ( Table1[Prices] ),
        FILTER ( Table1, Table1[Index] = EARLIER ( Table1[Index] ) + 1 )
    )
RETURN
    IF ( Table1[Prices] > NextRow, "-", "+" )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi,

 

How to refer previous adjacent cell value in Matrix.

 

Note: values in Matrix is Dax Measure.

I am using this formula. I almost have it.  BUT, I want to reference the previous value in the same column of my Calculated Field.

 

I am writing the calculated column in Column [Trade] but intellisense doesnt give it as an option.  I need to reference the previous value of the same column.  Whats the proper syntax? 
 

my formula should include these 3 conditions...

if current Price > previous price, +

if current Price < previous price, -

if current price = previous Price, previous row Column Value

 

i have this formula, but I cant reference same column name as the one i am in writing formula (Trade).  Can "EARLIER" be used alone (see last line, last condition)

 

Trade = VAR NextRow =   CALCULATE ( SUM ( INTC[Prices] ),
        FILTER ( INTC, INTC[Index] = EARLIER ( INTC[Index] ) -1 )    )
        RETURN   NextRow & " : " &
        IF ( INTC[Prices] > NextRow, "+", IF ( INTC[Prices] < NextRow, "-", IF(INTC[Prices] = NextRow, EARLIER(INTC[Trade],1))))

 

 

 

Stachu
Community Champion
Community Champion

I'm not sure how your last criteria (equal prices) would work as the column would have to iterate itself, otherwise this syntax should work:

=
VAR PrevRowIndex = MAX(INTC[Index]-1,1) //handles the first row
VAR PrevPrice = CALCULATE(SUM(INTC[Prices]),FILTER(ALL(INTC),INTC[Index]=PrevRowIndex)) //gets previous price
VAR Delta = INTC[Prices]-PrevPrice //calculates the delta
RETURN
IF(Delta<0,"-",IF(Delta>0,"+","~"))





Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

the problem is IF the price is the same as above (most recent) then use the same symbol.  I assume it can work because the formula is working down, so the previous value of the column has already been calculated, not sure why it cant be referenced in the formula for the next row, I dont see a 'circular reference' error, the values are independant.  here is a small sequence of how the logic should work.  sometimes there are consecutive equal values

 

IF( current price > previous value), "+",  IF( current value < previous price), "-" , use previous value of same column (either ( - or +)

 

Price, Trade

36.05

36.00, -

35.90, -

35,75, -

35.90, +

35.90, +

35.90, +

35.80, -

35.90, +

Stachu
Community Champion
Community Champion

I may be simplifying it, but DAX calculates the whole column at the same time, the visible order in the pivot is not really relevant for the calculation

Regardless - this should work, it looks up the Index that is lower than current index and where price is different than current price

=
VAR CurrentIndex = INTC[Index]
VAR CurrentPrice = INTC[Prices]
VAR PreviousIndex  = CALCULATE(MAX(INTC[Index]),FILTER(ALL(INTC),AND(INTC[Index]<CurrentIndex,INTC[Prices]<>CurrentPrice)))
VAR PreviousPrice = CALCULATE(SUM(INTC[Prices]),FILTER(ALL(INTC),INTC[Index]=MAX(PreviousIndex,1)))	//gets previous price
VAR Delta = INTC[Prices]-PreviousPrice	//calculates the delta
RETURN
IF(Delta<0,"-",IF(Delta>0,"+",BLANK()))






Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Phil_Seamark
Employee
Employee

Hi @ilcaa72

 

You can do it, but because of the way data is stored in the data model, you need to use a slightly different syntax.

 

Do you have an index column, or something with sequential numbers or dates?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.