Home Forums Power Pivot Calculated sales based on same date last year

Tagged: ,

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 3 years, 2 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #8990

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Hi I have imported my sales into power-pivot and need to calculate a 10% sales increase based on the same day of the week.

    First question is: can I have a column calculate based on the date-364 days so it equals the same day of the week. I can take the sales and x by 10% but I need the same weekday.

    Next part is harder. So I have monthly sales last February of 100,000, the stores goal is 100,000 x10%= $110,000 for the month. But I need to use the 1st of February last year which is a Wednesday and this year is a Thursday. So the same weekdays last year might total 95,000, so i need to take the same 28 days and x by 1.151 to = $110,000.

    I was thinking of using a calculated column. But not sure what to do.

     

    Thoughts?

     

    #8993

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

    With the intent of keeping the target of 110% over the prior year, I would take a less complicated approach.

    Thinking out loud for a minute, a person can create a measure with the SWITCH function that would execute a different slightly different formula depending on whether date, week, month, etc., is filtered in a cell of a pivot. For example, if a pivot cell is filtered on date, contrast sales with 110% of 364 days ago (to compare with the same day of week); but if the pivot cell is filtered on week, contrast with 110% of the sales between the week-start-date 364 days ago and the week-ending-date 364 days ago; but for all other calendar periods (which should be multiples of months), contrast with 110% of the same period last year.

    If you could put up some sample data in a workbook, we could see if we could hammer out the formula described in the paragraph above.

    From a previous post it looks like you are using Excel 2016, please advise if otherwise.

    Tom

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

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