Calculate blank projection values

snaraya — Tue, 21 Mar 2023 05:24:19 GMT

Hello everyone ,

My sample data is as below

BU	Year	projection	Output	factor	sum Reduction	efficiency	Reduction
A	2019	53.27	53.27131			0	0
A	2020	51.13	51.13091			0	0
A	2021	57.82	57.82309			0	0
A	2022	55.41	55.41486			0	-4.3
A	2023		53.91866	-0.027	0.116	0	-1.7
A	2024		55.85973	0.036	-0.216	0	-16
A	2025		57.87068	0.036	-0.792	0	7
A	2026		59.95402	0.036	-0.54	0	-2.6
A	2027		62.11237	0.036	-0.634	0	-2
A	2028		64.34841	0.036	-0.706	0	-5
A	2029		66.66496	0.036	-0.886	0	-10
A	2030		69.0649	0.036	-1.246	0	-2
B	2019	172.63	172.629			0	0
B	2020	168.54	168.5371			0	0
B	2021	165.2	165.1975			-0.326	-0.81
B	2022	151.13	151.1319			-9.513	-18
B	2023		160.5494	0.0623	-1.122	-5.22193	-5.64
B	2024		170.7406	0.0635	-1.5	-6.888	-1.62
B	2025		178.4903	0.0454	-1.146	-11.5	-23
B	2026		183.845	0.03	-1.448	-4	-10.25
B	2027		189.3604	0.03	-1.755	-3	-8
B	2028		195.0412	0.03	-1.995	0	-7.5
B	2029		200.8924	0.03	-2.22	-0.33	0
B	2030		206.9192	0.03	-2.22	0	0

To find the output column i used below formula

Output = VAR _tbl = TOPN ( 1, FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && NOT ( ISBLANK ( 'Table'[Projection] ) ) ), 'Table'[Year] ) VAR _FirstYear = MAXX ( _tbl, 'Table'[Year] ) VAR _BaseProjection = MAXX ( _tbl, 'Table'[Projection] ) RETURN IF ( ISBLANK ( 'Table'[Projection] ), _BaseProjection * PRODUCTX ( FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && 'Table'[Year] > _FirstYear && 'Table'[Year] <= EARLIER ( 'Table'[Year] ) ), 1 + 'Table'[Factor] ), 'Table'[Projection] )

sum reduction is a measure calculated from year 2023 onwards

2023=reduction 2022* factor

2024=reduction(2022+2023)*factor

2025= reduction(2022+2023+2024)*factor so on until 2030 for each BU

I want to adjust Output column as below, currently my formula has calculation until 1+factor.

Projection 2023= (Projection of 2022*(1+Factor)+effciency2023 +reduction2023-sum reduction2023)

Projection 2024-=(Projection of 2023(calculated in previous step) *(1+Factor) +effciency2024+reduction2024-sum reduction2024)

Projection 2025= (Projection of 2024(calculated in previous step) *(1+Factor) +effciency2025+reduction2025-sum reduction2025)and so on until 2030 for each BU

from 2019-2022 the data should be same

I tried addding efficiency, reduction & subtarcting sum reduction after the 1+factor bracket but it is just adding directly row to row and not at all subtracting may be because sum reduction is a measure

Can any one help to solve this

Re: Calculate blank projection values

Anonymous — Thu, 23 Mar 2023 02:27:04 GMT

Hi snaraya ,

It seems like you want to achieve recursion calculations in Dax.

AFAIK, current power bi not support these, you can take a look at the following blog about research on Dax recursion if helps:

Previous Value (“Recursion”) in DAX – Greg Deckler

Regards,

Xiaoxin Sheng

Re: Calculate blank projection values

snaraya — Fri, 24 Mar 2023 05:39:17 GMT

Hi Anonymous Thankou yes i need that , but my above dax code does that with 2columns(projection and factor) ..so i just need adjustment such that two more column is added and one subtracted but after product is done with 1+factor.

its really confusing for me now as i tried multiple ways to adjust the code.

topic Re: Calculate blank projection values in DAX Commands and Tips

Calculate blank projection values

Re: Calculate blank projection values

Re: Calculate blank projection values

Re: Calculate blank projection values