cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Calculate blank projection values

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

1 ACCEPTED SOLUTION
Helper I
3 REPLIES 3
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper I

Hi @v-shex-msft  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.

Helper I

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors