Forum Discussion
dkay84_PowerBI
Microsoft Employee
9 years agoPeriod 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
Microsoft Employee
9 years agoI 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?
Vvelarde
Community Champion
9 years ago
I don't touch the original DAX.
Only replace in slicer the weekid by week.
In a table visual disabled the totals because send you a error. (i think that you dont need the totals so i jump to manage this part in dax).