Forum Discussion
Period over Period Change Measure
- 9 years ago
having this sample table:
year and week are calculated columns
create a measure:
Delta% = VAR yearselected = VALUES ( Table1[Year] ) VAR weekselected = VALUES ( Table1[Week] ) VAR delta = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ) ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL ( Table1 ), Table1[Year] = yearselected - 1 && Table1[Week] = weekselected ) ) ) RETURN IF ( delta <> BLANK (), delta - 1, BLANK () ) - 9 years ago
I am not getting your solution to work with just the week number slicer. However, since in this case there will always just be current and previous year, I changed the code to the following and it works:
Delta% = VAR yearselected = MIN(Table[Year]) VAR weekselected = VALUES ( Table[Week#] ) VAR delta = DIVIDE ( CALCULATE ( SUM (Table[Sales] ) ), CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALL ( Table ), Table[Year] = yearselected && Table[Week#] = weekselected ) ) ) - 1 RETURN IF ( delta <> BLANK (), delta - 1, BLANK () )The red code is where I adjusted
So I have calculated columns which extract the year and week number from the WeekID column. The result looks like this in a table visual (after a slicer for week 41 has been selected):
I want my measure to calculate the resulting delta of week 41 YoY:
(201641 Sales)/(201541 Sales) -1 = 10,993,021/13,442,475 - 1 = -18.2%
having this sample table:
year and week are calculated columns
create a measure:
Delta% =
VAR yearselected =
VALUES ( Table1[Year] )
VAR weekselected =
VALUES ( Table1[Week] )
VAR delta =
DIVIDE (
CALCULATE ( SUM ( Table1[Sales] ) ),
CALCULATE (
SUM ( Table1[Sales] ),
FILTER (
ALL ( Table1 ),
Table1[Year]
= yearselected - 1
&& Table1[Week] = weekselected
)
)
)
RETURN
IF ( delta <> BLANK (), delta - 1, BLANK () )- dkay84_PowerBI9 years agoMicrosoft Employee
Thanks Vvelarde this works! However, I don't want the user to have to select a year, just a week.
In your example table, I would have a slicer with Week that would just have 1, 2, and 3. If they select 2, then week 2 for 2015 and 2016 would be displayed, and I want the resulting delta.
- Vvelarde9 years agoCommunity Champion
- dkay84_PowerBI9 years agoMicrosoft Employee
I tried replacing all instances of week with weekID in your code and it is giving me an error.
Would you mind posting the actual Delta formula?