Forum Discussion

ilcaa72's avatar
ilcaa72
Icon for Helper IV rankHelper IV
8 years ago
Solved

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...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    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()))




Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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, "-", "+" )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

 

How to refer previous adjacent cell value in Matrix.

 

Note: values in Matrix is Dax Measure.