Suppose we have a table
weeknum | revenue ------------------ 12 | 10000 12 | 10000 12 | 10000 13 | 10000 13 | 10000 13 | 10000 14 | 10000 14 | 10000 I tried to calculate the sum of the revenue for the previous weeknum:
Previous Revenue = CALCULATE( SUM(Table1[revenue]), FILTER( ALL(Table1[weeknum]), Table1[weeknum] = Table1[weeknum]-1 ) ) But, it is failed. Any Idea on this one
1 Answer
The statement you are passing to FILTER's FilterExpression needs to refer to the entry from Table1[weeknum] within the current row context.
This can be achieved by replacing
Table1[weeknum] = Table1[weeknum]-1
with
Table1[weeknum] = MIN(Table1[weeknum])-1
though it is perhaps better practice to create a variable, viz:
Previous Revenue := VAR ThisWeekNum = MIN( Table1[weeknum] ) RETURN CALCULATE( SUM( Table1[revenue] ), FILTER( ALL( Table1[weeknum] ), Table1[weeknum] = ThisWeekNum - 1 ) ) 
