Week over Week calculations - Previous Week Calculations - Power BI

[skaranam]

Hello All,

I am working on a power bi report and i have bugsfound for each week. I need to get previousweeknumber and then its bugs so that i can calculate WoW. Below is the summary,

Currently i have this

WeekNbr        BugsFound

34                       10

35                       20

36                       30

With formula in power bi, i calculated PrevWeekNbr

WeekNbr        BugsFound        PrevWeekNbr

34                       10                           33

35                       20                           34

36                       30                           35

What i need is (imagine week 33 bugs are 5)

WeekNbr        BugsFound        PrevWeekNbr   PrevWeekBugs

34                       10                           33                    5

35                       20                           34                   10

36                       30                           35                   20

I tried different formulas for new measure but did not get lucky. Any ideas ??

[DoubleJ]

Hi

Creating a colmn with this forumla might help (you might have to replace the semicolons with commas):

PrevWeekBugs = 
SUMX(
    FILTER(
        Bugs;
        Bugs[WeekNbr] = EARLIER(Bugs[PrevWeekNbr])
        )
    ;Bugs[BugsFound]
)+0

Hope this helps

JJ

[skaranam]

Hello JJ,

Thanks for your quick response and solution

I am using directquery models and i think thats the reason i am getting below error while using below logic.

Function 'SUMX' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Any ideas on this.

Thanks,

Sai

[DoubleJ · Accepted solution]

Hi Sai

How about this one:

PrevWeeksBugs2 = LOOKUPVALUE(Bugs[BugsFound];Bugs[WeekNbr];Bugs[PrevWeekNbr])+0

It is not a very nice solution I admit, someone might provide a better one.

JJ