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
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
I am still curious how you were able to implement your solution with just Week slicer (not year) but at least I have my measure
- RobJo9 years agoHelper I
i solved a simular issue on this by creating a calculated column in Power Query. where i integrateed the delta based on a index with a function (delta over a group of records, like by week). Anyone interested in this solution? send me a email and i will reply the example pbix. [email protected]