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 ) )

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy