Forum Replies Created
-
Posts
-
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!
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.
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.
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.
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.
