Home Forums Power Pivot Populate PY Week Sales on 52 Week Calendar

Tagged: 

This topic contains 9 replies, has 2 voices, and was last updated by  dmb33875 3 years, 11 months ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #4961

    dmb33875
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    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.

    #4972

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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

    #4994

    dmb33875
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    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.

    #4995

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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?

    #4999

    dmb33875
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    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.

    #5005

    dmb33875
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    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.

    #5006

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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

    #5009

    dmb33875
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    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.

    #5014

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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

    #5036

    dmb33875
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    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!

Viewing 10 posts - 1 through 10 (of 10 total)

The forum ‘Power Pivot’ is closed to new topics and replies.