This topic contains 9 replies, has 2 voices, and was last updated by 
-
Posts
-
I’m looking for a way to pull in prior year, prior week sales when using a 52 week calendar. I tried a formula:
=CALCULATE(‘Reporting Data'[Total Actual],’Reporting Data'[CY Week]=’Reporting Data'[PY Week])
Thinking that if I’m pulling second quarter data then week 14 for 2016 would pull week 14 for 2015 as well? Unfortunately it results in an error.
Any suggestions would be appreciated.
What error was raised?
Looks like your model consists of a single table. To get the results you want, I believe you should at least have two tables, one for data and one for dates/weeks lookup.
What version of Excel are you using?
Why not use a 365 day year calendar with a column to indicate week number?
Tom
Thanks for the response. I’m using Excel 2010 and I’m using a 52 week calendar because that’s what my company uses for reporting. I tried a slightly different formula:
=CALCULATE(‘Reporting Data'[Total Actual],SAMEPERIODLASTYEAR(‘Reporting Data'[PY Week Ended]))
For [PY Week Ended] if the week ended is 6/18/2016 then it returns 6/20/2015 (factoring for the leap year). I don’t get an error now, but it doesn’t return the prior year’s same week sales value either.
Hi,
The built-in time intelligence functions (like same SAMEPERIODLASTYEAR) are designed to work with the “classic” 365-days-per-year calendar.
Do your calendar weeks follow the 4-4-5 pattern for months?
Yes…I thought I could trick / facilitate by creating a prior year column. It is indeed a 4-4-5 schedule that we are on.
I’m trying to do this in the pivot and I think it needs to be done within the data table. I’ve got data in rows with:
week ended
profit center
region
country
actual
plan
forecast
from there I added formulas for:
year
quarter
month
week number
same day prior year (to link up to the week ended)
So in this new formula I’m trying to pull [actual] when [week ended] equals [same day prior year] but the unique identifier could by the country. As an example on the line for 6/18/2016 for Singapore where same day prior year equals 6/20/2015 then I want to pull that actual value from the line for 6/20/2015 for Singapore and return it on that line for 6/18/2016 for Singapore.
I’m assuming this is easiest way? I could reload my data with a column for the prior year values, but it’s better to use a formula vs. manually loading as we continue to build the data.
Hi,
Please see pages 245 – 261 of “Power Pivot and Power BI” by Rob Collie and Avi Singh which discusses custom calendars (much of the discussion focuses on the 4-4-5 calendar).
It still looks like you are using a single table data model like you would with a pivot table in regular Excel. Is this so?
Tom
I got the book about a week ago but am only up to page 100 something in between some travel.
I’m like a kid at Christmas digging into to work already but I’ve been trying to do as the book advises and read it straight through.
Yes…I am using a single data table at this point.
I appreciate all your help. I’ll let you know how it works out.
Hi,
I am mightily convinced, that the way up and out with the minimum amount of learning curve is with a multi-table model.
If I can help you build a multi-table model (which I do not think will be that difficult), let me know.
Tom
It worked…GFITW! Thanks for your assistance. I have converted to a multi-table model as well. This will be helpful as it should allow me to forecast updates for comparison. Good stuff!
The forum ‘Power Pivot’ is closed to new topics and replies.