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?   T...
Need Help
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.