+ Reply to Thread
Results 1 to 5 of 5

Calculated Columns in Power Query for Staggered Product Launches

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    4

    Calculated Columns in Power Query for Staggered Product Launches

    Hi,

    I'm creating a financial model where a "Product" will be launched several times over the coming years. The Product will have a predictable financial profile but I'm trying to build in flexibility to run scenarios where we specify how many of these products are launched in a given year. I'm trying to calculate the total amount for a given account e.g. Revenues in a year, taking into account the revenue profiles of all the products launched in the previous year.

    E.g. If a Product produces Revenues of 100, 200, and 400 in Years 1-3 respectively, and I launch 1 Product in each of the first 3 years, then the Revenues in Year 3 should be 600 (400 from the product launched in Year 1, 200 from the product launched in Year 2, and 100 from the product launched in Year 3).

    I have tabular data which includes:

    1) Annual Account profiles for each product e.g. Revenues from Year 1 through Year X

    2) Profile of the number of products launch in a given year i.e. Product launches in each of Years 1 through X

    I've figured out a way to do this with helper rows where I transpose the Product launches row (2) and then multiply it by the account profiles (1). The problem with this is that I'm modeling over many years and this creates an unnecessary amount of these "helper rows" for each account that I model e.g. Revenues, COGS, Opex, etc.

    I'm trying to figure out a way to do the above programmatically through Power Query but I'm getting tripped up by the staggered nature of this model.

    I've attached an Excel example, which is hopefully better than my explanation. Essentially, I've got the cells with blue text in data tables and I'm trying to get the cells highlighted in yellow, without using the cells with red text.

    I know this is a mouthful but I'd appreciate any help on this.

    Note: I did just post this question (Calculated Columns for Staggered Product Launches) on a site called My Online Training Hub. I didn't realize that this site discourages multiple posting until I read it here.

    Thanks in advance!

    Kind regards,

    Uwais
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,488

    Re: Calculated Columns in Power Query for Staggered Product Launches

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future

    https://www.mrexcel.com/board/thread...nches.1140720/

    https://chandoo.org/forum/threads/ca...aunches.44711/

    https://www.excelguru.ca/forums/show...oduct-Launches

  3. #3
    Registered User
    Join Date
    08-21-2013
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Calculated Columns in Power Query for Staggered Product Launches

    Thanks for raising this alansidman. In addition to those three posts, I also posted this question on Reddit and MyOnlineTrainingHub. I tried to paste the links here but am getting the error: "You are not allowed to post any kinds of links, images or videos until you post a few times."

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,488

    Re: Calculated Columns in Power Query for Staggered Product Launches

    Type the links out. You need to have 10 posts to provide a link.
    Make sure you read the link in my post on why you need to do this. While you may think that you will get a faster answer, once people see that you have crossposted, many will ignore you so it actually works against you especially if you crosspost to multiple sites.

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,278

    Re: Calculated Columns in Power Query for Staggered Product Launches


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Custom Permutations Column in Power Query from two columns in the same Query
    By PaintPaddy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2019, 02:48 PM
  2. [SOLVED] Store Power Query table and add calculated field
    By jaryszek in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-01-2019, 08:18 AM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. Power Query - calculated true or false show result of table????
    By janljan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2018, 11:23 PM
  5. Power Pivot help on creating calculated columns
    By Pavan.Sada.PS in forum Excel General
    Replies: 1
    Last Post: 08-21-2016, 05:36 PM
  6. Staggered sum product formula
    By IronCladRooster in forum Excel General
    Replies: 3
    Last Post: 03-05-2015, 05:25 AM
  7. Formula for quarterly product launches
    By Bwatson123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2012, 02:24 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts