Previous Week variance

[Anonymous]

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

[Anonymous]

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]

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.

@Anonymous@kcantor

[Anonymous]

Hi @Anonymous,

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

Can you please share us the file to test? I think it will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[motoroto]

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

[Anonymous]

Hi All,

No was a fully fledged date table.

W

[kcantor]

@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]

hi @kcantor

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

Thanks

Chris

[kcantor]

@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]

Im guessing it would be something like

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

?