Forum Discussion
Helper IVReference Previous Value in adjacent column
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()))
Community ChampionHi 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, "-", "+" )
Helper IVI 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
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,"+","~"))- ilcaa72
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
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()))