Results 1 to 4 of 4

Thread: Calculated Columns in Power Query for Staggered Product Launches

  1. #1
    Neophyte urazack's Avatar
    Join Date
    Jul 2020
    Excel Version

    Calculated Columns in Power Query for Staggered Product Launches

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Below is a link to 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.
    Excel example:
    I know this is a mouthful but I'd appreciate any help on this.
    Thanks in advance!
    Kind regards,

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Ipswich, Suffolk, England
    Excel Version
    Office 365 Subscription
    This thread has been posted on at least three other forums. You will need to provide links to those threads here before we can offer any help, please. Thanks.
    Enthusiastic self-taught user of MS Excel!

  3. #3

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Excel Version
    In the attached, Sheet1 has all the information on it for your solution (Your Model sheet isn't referred to).
    The two small tables on the left are the source data tables.
    The green table is a Power Query table.
    The 4th (Pivot) table is that green table pivoted. You could do all the aggregating in Power Query if you wanted, but I suggest that having the intermediate green table (visible or not) can lead to:
    1. More flexibility on how you pivot that data
    2. With only a small change or two, you can tweak the Power Query to retain other data such as the identity (eg. Launch year and specific id of that launch (2020.1, 2020.2 sort of thing)) which you can choose to include in your pivot.

    ps. note that because I've chosen to show the full table (green) you'll need to click refresh all twice, once to refresh the query, and a second time to update the pivot table.
    Attached Files Attached Files
    Last edited by p45cal; 2020-07-22 at 12:57 PM.

Posting Permissions

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