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 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
9 years agoCommunity Champion
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).