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




Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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?