Forum Discussion
Getting the Previous Value in a Matrix with 7 rows
You have interesting data including negative numbers. Calculating a WoW with a mix of positive and negative numbers is extremely subjective. Make sure you agree with the formula.
Here's my (subjective) version:
Rev1 change = var pr1 = CALCULATE(sum(test[Rev1]),DATEADD('Calendar'[Date],-7,DAY)) return divide(sum(test[Rev1])-pr1,abs(pr1),1)However your x axis is driven by a fact date (end of snapshot week) and not by the dimension date as it should be . You will want to correct that too ( or modify the above formula)
Super UserYou have interesting data including negative numbers. Calculating a WoW with a mix of positive and negative numbers is extremely subjective. Make sure you agree with the formula.
Here's my (subjective) version:
Rev1 change =
var pr1 = CALCULATE(sum(test[Rev1]),DATEADD('Calendar'[Date],-7,DAY))
return divide(sum(test[Rev1])-pr1,abs(pr1),1)
However your x axis is driven by a fact date (end of snapshot week) and not by the dimension date as it should be . You will want to correct that too ( or modify the above formula)
Thank you for this but the formula for `pr1` isn't quite right.
My bad, my explanation for expected output wasn't clear.
As you will notice that not every change is being highlighted as red or green.
For example, Rev1 on row 6 should be in green. Value went up from 30527.25 to 30346.50
pr1 =
var eosw = max(test[EndOfSnapshotWeek])
RETURN CALCULATE(
sum(test[Rev1]),
test[EndOfSnapshotWeek]=eosw-7
)I require it to match this visual calculation
vcPr1 = PREVIOUS([Rev1], 1, COLUMNS)As you can see they don't match
My X axis is driven by the fact table because the active relationship between the fact table and the calendar table is on a different date column in the fact table.
- lbendlin
As you can see in my example I included a neutral area of +/- 5%. Feel free to modify that.
- Cool, thanks. All working now