Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Previous Week variance

Hi,

 

I want to pull in the previous week into the table, so for example next to week thritheen i want to show week 12 and then do a variance on it.

 

How would I pull in the previous week?

 

Thanks

 

 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Im guessing it would be something like

     

    calculate(sum(sales), dateadd(calenderdate)-date(weeknum(calenderdate)-7))

     

    ?

  • kcantor's avatar
    kcantor
    Community Champion
    9 years ago

    Anonymous

    first, make sure you have a sepearte date table. Time intelligence needs that in order to work.

    Then, write the calculation for sales:

    Sales Qty = SUM('TableName'[NetSalesQTY])

    Then you can write sales for last week:

    Last Week Sales Qty = CALCULATE([Sales Qty], DATEADD,('DateTable'[DateKey], -7, DAY))

    It is always better to build measure in steps. That way, if one breaks, it is easy to fix. The words in bold above would need to be changed to match your table names and column names.

    For the variance, use the measure names we just created:

    Variance = [Sales Qty]-[Last Week Sales Qty]

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      hi kcantor

       

      Thanks for that but when I put it in my table I get a contigous date error?

       

      Thanks

       

      Chris

      • kcantor's avatar
        kcantor
        Community Champion
        9 years ago

        Anonymous

        That error means that you do not have a complete date table (with no missing dates) or that your columns are not filtered correctly. 

        Make sure your date table is complete and pull your week numbers from there. You may also need to filter by year or put year on the columns as well.

        It could also be that you are pulling your week numbers from the fact table instead of your date table.  If you share a sample of your data I would be happy to look at it a little closer.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    Can you please share some sample data to test ? You can write the formula without calendar table.

    In addition, I think your records not contain the date field but exist the week number, right?

     

    Regards,

    Xiaoxin Sheng 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi All,

       

      No was a fully fledged date table.

       

      W

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi All,

       

      No was a fully fledged date table.

       

      Was trying to ahcieve this in excel and was getting the error.  When I then put it into PowerBI it worked first time....

       

       

      Wierd..

       

      Thanks for help though guys.

       

      Anonymouskcantor

      • motoroto's avatar
        motoroto
        Frequent Visitor
        9 years ago

        Hello, I have a similar problem with performing calculations based on a previous dates.

        What I have been asked is calculate difference between every single day and the average of the same weekday of

        the last 4 weeks.

        So far I have managed to do so by creating a measure with the following sintax

         

        Var % = [Sales]/

        ((CALCULATE([Sales];DATEADD(‘DATE’[Date];-7;Day))+

        CALCULATE([Sales];DATEADD(‘DATE’[Date];-14;Day))+

        CALCULATE([Sales];DATEADD(‘DATE’[Date];-21;Day))+

        CALCULATE([Sales];DATEADD(‘DATE’[Date];-28;Day)))/4) -1

         

        But I have the feeling there must be a simpler way to do so. 

         

        Thanks in advance