Calculate Rolling Total based on varying % changes year over year

[Justin_Spiegel]

Hello - 

I am trying to calculate a forecasted projection for a total based on growth rates that change year over year. My formula will get the first year a projection is needed, but for successive years it is multiplying by against the base value (and not the most recent value). 

Data is set as below:

Fruit	Demand	Year	Growth	New Growth Value
Apples	20	2025		20
Apples	20	2026	2.5%	=20*1.025
Apples		2027	3.5%	=needs to be the above result times 3.5%
Apples		2028	4.5%	=needs to be result above times 4.5%

Instead it is just multiplying the 3.5% times the original demand number of 20.

An item that is very simple in Excel but I am having a hard time translating it into DAX (either as a measure or a calculated column). 

[Justin_Spiegel]

Hi All - 

I have spent the last several days trying multiple methods so I could accept both solutions, but I believe a difference in what I showed above vs what my model is calculating against is critical. In the table referenced in the thread above, Demand was blank for 2027+. However, in all actuality, the values of 20 would be relevant for each given year, but I want to override the demand with the Calculated Growth instead. 

Fruit	Demand	Year	Growth	New Growth
Apples	20	2025	0.00%	20
Apples	20	2026	3.50%	20*1.035 = 21.22
Apples	20	2027	4.25%	21.22*1.0425 = 22.17
Apples	20	2028	3.50%	22.17*1.0350 = 22.95

I appreciate the help provided so far, I think this is close to being solved. 

[TheoC]

Hi @Justin_Spiegel 

Have attached a PBIX for you.  You will need to create a Calculated Column like this:

New Growth = 

VAR _1 = 'Table (2)'[Demand] * (1 + 'Table (2)'[Growth] )
VAR _2 = MAXX ( FILTER ( 'Table (2)' , 'Table (2)'[Fruit] = EARLIER ( 'Table (2)'[Fruit] ) ) , 'Table (2)'[Demand] )
VAR _3 = IF ( ISBLANK ( _1 ) , _2 * ( 1 + 'Table (2)'[Growth] ) , _1 )

RETURN

_3

Output gives you the following:

Then you can create a table like this:

Hope this helps!

Theo 🙂

[Justin_Spiegel]

Hi TheoC - 

Thanks for the insight. This is close and is an area I have been able to get to. It is the next step that is challenging when I want to do the growth rate against a previously calculated value. Example below in the table:

Fruit	Demand	Year	Growth	New Growth
Apples	20	2025	0.00%	20
Apples	20	2026	3.50%	20*1.035 = 21.22
Apples		2027	4.25%	21.22*1.0425 = 22.12

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[TheoC]

Hi @Justin_Spiegel 

Please see attached PBIX. Hopefully this gives you what you're after 🙂

Theo 

[Justin_Spiegel]

Hi All - 

I have spent the last several days trying multiple methods so I could accept both solutions, but I believe a difference in what I showed above vs what my model is calculating against is critical. In the table referenced in the thread above, Demand was blank for 2027+. However, in all actuality, the values of 20 would be relevant for each given year, but I want to override the demand with the Calculated Growth instead. 

Fruit	Demand	Year	Growth	New Growth
Apples	20	2025	0.00%	20
Apples	20	2026	3.50%	20*1.035 = 21.22
Apples	20	2027	4.25%	21.22*1.0425 = 22.17
Apples	20	2028	3.50%	22.17*1.0350 = 22.95

I appreciate the help provided so far, I think this is close to being solved. 

[TheoC]

Hi @Justin_Spiegel the numbers you have posted in your reply to the solution I put forward do not match those in your original post.  I am not sure why yours are changing, however, the solution I have put forward is in line with your actual post. I am unsure why your reply comment has different figures. Can you let me know why this has changed?  Thanks, Theo.

[Justin_Spiegel]

Hi @TheoC - 

I apologize, I did not realize I had changed the growth rates values in my recent response. The original table is reflected below. The actual growth rate % values aren't overly important, I think the bigger issue is that the first table I provided showed "DEMAND" was empty in 2027 and 2028 and I wanted that field to populate based on either the given value OR the calculated value (post 2027).

In my model, the "DEMAND" section is populated with data, but I do not want to use that value for anything >4 years out. So I use my "DEMAND" data up until 2026 and then it switches to a calculation based on various growth rates (example included in table below). 

Fruit	Demand	Year	Growth	Calculated Value I'm seeking
Apples	20	2025	0%	=DEMAND (20 in this case)
Apples	20	2026	2.50%	=DEMAND (20 in this case and is disregarding the supposed growth rate)
Apples	20	2027	3.50%	=DEMAND * growth rate - 20*3.5% = 21.22
Apples	20	2028	4.50%	=2027 Calculated Demand * Growth rate- 21.22*4.5% = 22.17

Does this make sense? It is one of those items that is very simple to have calculated on a cell-level basis within Excel, but remains challenging for me calculating with PBI. 

Thanks,

Justin

[TheoC]

Hi @Justin_Spiegel 

Unfortunatley, it is significantly important.  If there is a Demand figure in the Demand column after 2026, then the solution is different to that where it does not have a figure.  It's really important in future that you do provide all the information so that we can provide you with the right solution.

Now knowing that there is data in all rows of your Demand column, can you please provide a sample file of your data so that a proper solution can be provided.  In this sample data, please add a column that has the output that you want.  Therefore, please ensure you have all of the rows that you refer to and that they have all the data in the identifical way that your actual model has (e.g. if you have 20 rows of data representing 20 years and each has 20 different demands, please have all of these in the sample data file), as well as and ensure that the file also contains the columns:

• Fruit
• Demand
• Year
• Growth
• Expected Outcome (with Calculations)

Once you have provided this, I'll be able to assist further. However, without the above, there will continue to be misunderstandings / changes to requirements, etc., with the only thing missing being a valid solution to the issue at hand.

Hope this makes sense and reach out if anything from the above isn't clear.

Thanks,

Theo