Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone ,
i have been working on below data for 3days and couldnt find a correct solution
My sample data is as below
BU | Year | Projection | Factor | Index | Output |
A | 2019 | 53.27 | 0 | 53.27 | |
A | 2020 | 51.13 | 1 | 51.13 | |
A | 2021 | 57.83 | 2 | 57.83 | |
A | 2022 | 59.4 | 3 | 59.4 | |
A | 2023 | -0.027 | 4 | 57.7962 | |
A | 2024 | 0.036 | 5 | 61.5384 | |
A | 2025 | 0.036 | 6 | 61.5384 | |
A | 2026 | 0.036 | 7 | 61.5384 | |
A | 2027 | 0.036 | 8 | 61.5384 | |
A | 2028 | 0.036 | 9 | 61.5384 | |
A | 2029 | 0.036 | 10 | 61.5384 | |
A | 2030 | 0.036 | 11 | 61.5384 | |
B | 2019 | 172.63 | 12 | 172.63 | |
B | 2020 | 168.54 | 13 | 168.54 | |
B | 2021 | 165.2 | 14 | 165.2 | |
B | 2022 | 151.13 | 15 | 151.13 | |
B | 2023 | 0.062313 | 16 | 183.3871 | |
B | 2024 | 0.063477 | 17 | 183.588 | |
B | 2025 | 0.045389 | 18 | 180.4655 | |
B | 2026 | 0.03 | 19 | 177.8089 | |
B | 2027 | 0.03 | 20 | 177.8089 | |
B | 2028 | 0.03 | 21 | 177.8089 | |
B | 2029 | 0.03 | 22 | 177.8089 | |
B | 2030 | 0.03 | 23 | 177.8089 |
to find the out column Output i used below DAX
Output =
VAR CurrentRow = 'Table'[Projection]
VAR PrevRow = CALCULATE( LASTNONBLANK('Table'[Projection],0 ), FILTER( 'Table', 'Table'[Index] < EARLIER('Table'[Index]) && NOT(ISBLANK('Table'[Projection])) ) )
VAR Factor = 'Table'[Factor] RETURN IF( ISBLANK(CurrentRow), PrevRow * (1 + Factor), CurrentRow )
the logic which i need is
for 2023- Projection of 2022*(1+Factor)
for 2024- Projection of 2023(calculated in previous step) *(1+Factor)
for 2025- Projection of 2024(calculated in previous step) *(1+Factor) and so on until 2030 for each BU
but my formula is using last non blank value of 2022 for calculating everything,so my 2023 value for both BU is correct from 2024 the value is wrong as it should use 2023 value to caculate 2024 and 2024 value for 2025 and so on
from 2019-2022 the data should be same
any help is appreciated
Solved! Go to Solution.
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]
)
is the result correct?
Output =
VAR _tbl =
TOPN (
1,
FILTER (
'Table',
'Table'[BU] = EARLIER ( 'Table'[BU] )
&& NOT ( ISBLANK ( 'Table'[Projection] ) )
&& 'Table'[Year] < EARLIER ( 'Table'[Year] )
),
'Table'[Year]
)
VAR _FirstYear =
MAXX ( _tbl, 'Table'[Year] )
VAR _BaseProjection =
MAXX ( _tbl, 'Table'[Projection] )
VAR _tbl2 =
FILTER (
'Table',
'Table'[BU] = EARLIER ( 'Table'[BU] )
&& 'Table'[Year] > _FirstYear
&& 'Table'[Year] <= EARLIER ( 'Table'[Year] )
)
VAR _tbl3 =
ADDCOLUMNS (
_tbl2,
"@f", 1 + 'Table'[factor],
"@adj",
VAR _f =
PRODUCTX (
WINDOW (
1,
REL,
-1,
abs,
SUMMARIZE ( _tbl2, 'Table'[BU], 'Table'[Year], 'Table'[factor] ),
ORDERBY ( 'Table'[Year] )
),
1 + 'Table'[factor]
)
RETURN
( 'Table'[efficiency] + 'Table'[ Reduction] - 'Table'[sum Reduction] )
* IF ( _f, _f, 1 )
)
RETURN
IF (
ISBLANK ( 'Table'[projection] ),
_BaseProjection * PRODUCTX ( _tbl3, [@f] )
+ SUMX ( _tbl3, [@adj] ),
'Table'[projection]
)
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]
)
Thankyou for this output , things got little complicated as i need adtional column where we add two columns and subtract one other columns . sum reduction is a measure
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 |
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 you help me in adjusting this fromula in any way? wasted too many days in this.
Hi @wdx223_Daniel is there a possibility of this working out or its not possible in dax? can you suggest ?
is the result correct?
Output =
VAR _tbl =
TOPN (
1,
FILTER (
'Table',
'Table'[BU] = EARLIER ( 'Table'[BU] )
&& NOT ( ISBLANK ( 'Table'[Projection] ) )
&& 'Table'[Year] < EARLIER ( 'Table'[Year] )
),
'Table'[Year]
)
VAR _FirstYear =
MAXX ( _tbl, 'Table'[Year] )
VAR _BaseProjection =
MAXX ( _tbl, 'Table'[Projection] )
VAR _tbl2 =
FILTER (
'Table',
'Table'[BU] = EARLIER ( 'Table'[BU] )
&& 'Table'[Year] > _FirstYear
&& 'Table'[Year] <= EARLIER ( 'Table'[Year] )
)
VAR _tbl3 =
ADDCOLUMNS (
_tbl2,
"@f", 1 + 'Table'[factor],
"@adj",
VAR _f =
PRODUCTX (
WINDOW (
1,
REL,
-1,
abs,
SUMMARIZE ( _tbl2, 'Table'[BU], 'Table'[Year], 'Table'[factor] ),
ORDERBY ( 'Table'[Year] )
),
1 + 'Table'[factor]
)
RETURN
( 'Table'[efficiency] + 'Table'[ Reduction] - 'Table'[sum Reduction] )
* IF ( _f, _f, 1 )
)
RETURN
IF (
ISBLANK ( 'Table'[projection] ),
_BaseProjection * PRODUCTX ( _tbl3, [@f] )
+ SUMX ( _tbl3, [@adj] ),
'Table'[projection]
)
Thankyou so much for this formula, i converted my Measure into a column and now everything works fine.
yes the output is exactly correct..but my sum reduction is measure which i calculated and idk why its not subtracting it at all ...
maybe the measure can not get the result in the row context of _tbl2
Thankyou so much for this. Perfect
Output =
VAR _tbl =
TOPN (
1,
FILTER (
'Table',
'Table'[BU] = EARLIER ( 'Table'[BU] )
&& NOT ( ISBLANK ( 'Table'[Projection] ) )
&&'Table'[Index]<EARLIER( 'Table'[Index] )
),
'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]
)
small revise, to consider the case that the years with projection value are not consecutive. such as the year 2026 is not blank, the old code will get a wrong value.
@snaraya Hi!
Output =
VAR CurrentRow = 'Table'[Projection]
VAR PrevRow = CALCULATE(
LASTNONBLANK('Table'[Output], 0),
FILTER('Table', 'Table'[Index] < EARLIER('Table'[Index]) && NOT(ISBLANK('Table'[Projection])))
)
VAR Factor = 'Table'[Factor]
RETURN IF(
NOT(ISBLANK(CurrentRow)), CurrentRow * (1 + Factor),
IF(
NOT(ISBLANK(PrevRow)), PrevRow * (1 + Factor),
BLANK()
)
)
BBF
I think i confused you ,
the output column is what i am getting using the formula mentioned in the post.
using your formula i am still getting same values which i am getting in output column of table..
values in my output column should be as follows
for 2023- Projection of 2022*(1+Factor of 2023)
for 2024- Projection of 2023(calculated in previous step) *(1+Factor of 2024)
for 2025- Projection of 2024(calculated in previous step) *(1+Factor of 2025) and so on until 2030 for each BU
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |