Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
snaraya
Helper II
Helper II

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 (
        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
3 REPLIES 3
Anonymous
Not applicable

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

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.