Forum Discussion
dkay84_PowerBI
9 years agoMicrosoft Employee
Period over Period Change Measure
I am building a report that will calculate deltas for sales and quantity. The sales and quantity values returned from the query to the DB are aggregated, so I have a table that returns Product Name,...
- 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
dkay84_PowerBI
9 years agoMicrosoft Employee
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
RobJo
9 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]