CODEWITHSUNDEEP
powerbidax
fajar haruna
fajar haruna

Reputation: 17

SUM for previous Weeknum in Power BI

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

Upvotes: 1

Views: 51

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9072

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

Upvotes: 2

Related Questions