# Calculated Columns in Power Query for Staggered Product Launches

1. ## 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.

Kind regards,

Uwais

2. ## 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.

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

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

3. ## 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. ## 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. ## Re: Calculated Columns in Power Query for Staggered Product Launches

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