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
