Showing results for 
Search instead for 
Did you mean: 

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

Helper I
Helper I

Calculate blank projection values

Hello everyone ,


My sample data is as below


BUYearprojectionOutputfactorsum Reductionefficiency Reduction
A201953.2753.27131  00
A202051.1351.13091  00
A202157.8257.82309  00
A202255.4155.41486  0-4.3
A2023 53.91866-0.0270.1160-1.7
A2024 55.859730.036-0.2160-16
A2025 57.870680.036-0.79207
A2026 59.954020.036-0.540-2.6
A2027 62.112370.036-0.6340-2
A2028 64.348410.036-0.7060-5
A2029 66.664960.036-0.8860-10
A2030 69.06490.036-1.2460-2
B2019172.63172.629  00
B2020168.54168.5371  00
B2021165.2165.1975  -0.326-0.81
B2022151.13151.1319  -9.513-18
B2023 160.54940.0623-1.122-5.22193-5.64
B2024 170.74060.0635-1.5-6.888-1.62
B2025 178.49030.0454-1.146-11.5-23
B2026 183.8450.03-1.448-4-10.25
B2027 189.36040.03-1.755-3-8
B2028 195.04120.03-1.9950-7.5
B2029 200.89240.03-2.22-0.330
B2030 206.91920.03-2.2200



To find the output column i used below formula





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





sum reduction is a measure calculated from year 2023 onwards

2023=reduction 2022* 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 


Community Support
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


Xiaoxin Sheng

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

Hi @v-shex-msft  Thankou yes i need that , but my above dax code does that with 2columns(projection and factor) 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.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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