How to build dynamic forecast based on changing CAGRs

[TMC_Belisar]

Dear community,

I'm currently trying to build demand forecasts based on existing CAGRs which differ in Operating Division, Strategic Relevant Market (SRM) and time frame (2025-2030 vs 2031-2035).

The result should look something like this:

I'v ered through several posts in this forum but the closest request (Solved: Cumulative Forecast - Microsoft Fabric Community) came with two major obstacles for which I could not find a solution:

• summarize(): When using this function, the CAGRs get only summarized which is methematically wrong (we need a"power"-relationship between the years) and the demand don't starts with "1" or "100%". 
• I cannot selectively chose different CAGRs for different years. Comeing from differend programming languages I would chose a "for" function. However, even when implementing a "switch" function, I cannot ask for distinguishing between different years.

To simplify my request I created the following two tables as my source of data:
'CAGR'
 

Divison	Strategic Relevat Market	CAGR	Time frame
A	SRM 1	1%	2025-2030
A	SRM 1	2%	2031-2035
A	SRM 2	-1%	2025-2030
A	SRM 2	-2%	2031-2035
B	SRM 3	5%	2025-2030
B	SRM 3	10%	2031-2035
B	SRM 4	3%	2025-2030
B	SRM 4	-3%	2031-2035

'Calendar'

Date
01.01.2025
01.01.2026
01.01.2027
01.01.2028
01.01.2029
01.01.2030
01.01.2031
01.01.2032
01.01.2033
01.01.2034
01.01.2035

Many thanks in advance for your support! I really love this forum 🙂

[MFelix]

Hi @TMC_Belisar ,

Make the following:

• Split the Time frame between start and end column:

Now add the following two  measure:

Total Values = 
VAR TEMP_TABLE = 
FILTER(


    	FILTER(CROSSJOIN(
		FILTER(
			'Range',
			'Range'[Start] <= MIN('Calendar'[Year]) && 'Range'[End] >=  MIN('Calendar'[Year]) 
		),
	
			VALUES('Calendar'[Year])		), 'Calendar'[Year] >= 'Range'[Start] && 'Calendar'[Year] <= 'Range'[End])
, 'Calendar'[Year] <= MAX('Calendar'[Year]))

RETURN SUMX(TEMP_TABLE,
 Range[CAGR])

Final Value= 1 + sumx( FILTER(ADDCOLUMNS(ALL('Calendar'),"FinalValue", [Total Values]),'Calendar'[Year] <= MAX('Calendar'[Year])),[FinalValue])

[TMC_Belisar]

Hi Miguel,

thank you very much! Your suggestion solves the issue with two different CAGRs within the larger time frame.

Unfortunately, again the sumx() function is only adding the CAGRs up, not multiplying the previous years value.

Do you also have a suggestion how incorporate the previous years value of your draft into some kind of productx() function?

Many thanks

[MFelix · Accepted solution]

Hi @TMC_Belisar ,

I tough you were only summing up the values in this case use the PRODUCTX ( 😊)function redo your final value to:

Final Value = PRODUCTX( FILTER(ADDCOLUMNS(ALL('Calendar'),"FinalValue", 1+[Total Values]),'Calendar'[Year] <= MAX('Calendar'[Year])),[FinalValue])

Please check if the results are correct but believe they are.

[TMC_Belisar]

Perfect, now it works 🙂