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