Forum Discussion

skaranam's avatar
skaranam
Frequent Visitor
9 years ago
Solved

Week over Week calculations - Previous Week Calculations - Power BI

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's avatar
    DoubleJ
    9 years ago

    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

3 Replies

  • DoubleJ's avatar
    DoubleJ
    Solution Supplier
    9 years ago

    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's avatar
      skaranam
      Frequent Visitor
      9 years ago

      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's avatar
        DoubleJ
        Solution Supplier
        9 years ago

        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