Forum Discussion
snaraya
3 years agoHelper II
find next value using previous value
Hi All,
i have below table, my output should be a column which uses
Target previous row-(Target previous row*0.3863636363)
eg: 2019= 2019
2020= Target2019-(Target 2019*0.3863636363)
2021= Traget 2020-(Target 2020*0.3863636363)
i tried using below DAX formula but the value is only correct for 2020, as its using last nonblank value of year 2019 for finding growth_Target all the years which is not correct
growth_Target =
IF(ISBLANK('Table'[Target]),
VAR _curRank = 'Table'[Index]
VAR _lastNonBlankRank = MAXX(FILTER('Table', 'Table'[Index]< _curRank && NOT ISBLANK('Table'[Target]) ), 'Table'[Index])
VAR _lastNonBlankValue = LOOKUPVALUE('Table'[Target], 'Table'[Index], _lastNonBlankRank)
RETURN
_lastNonBlankValue-( _lastNonBlankValue * POWER(0.3863636363, _curRank - _lastNonBlankRank)),
'Table'[Target])
| Index | Year | bu | Target | growth_Target |
| 0 | 2019 | a | 19.791 | 19.79096042 |
| 1 | 2020 | a | 12.14445299 | |
| 2 | 2021 | a | 16.836628 | |
| 3 | 2022 | a | 18.6495138 | |
| 4 | 2023 | a | 19.34994695 | |
| 5 | 2024 | a | 19.62056885 | |
| 6 | 2025 | a | 19.72512731 | |
| 7 | 2026 | a | 19.7655249 | |
| 8 | 2027 | a | 19.78113306 | |
| 9 | 2028 | a | 19.78716348 | |
| 10 | 2029 | a | 19.78949342 | |
| 11 | 2030 | a | 19.79039362 | |
| 12 | 2019 | b | 3.177 | 3.176699292 |
| 13 | 2020 | b | 1.949338202 | |
| 14 | 2021 | b | 2.702491598 | |
| 15 | 2022 | b | 2.993482683 | |
| 16 | 2023 | b | 3.105911056 | |
| 17 | 2024 | b | 3.149349292 | |
| 18 | 2025 | b | 3.166132246 | |
| 19 | 2026 | b | 3.17261657 | |
| 20 | 2027 | b | 3.175121876 | |
| 21 | 2028 | b | 3.176089836 | |
| 22 | 2029 | b | 3.17646382 | |
| 23 | 2030 | b | 3.176608314 |
can someone help what exactly is wrong
- growth_Target =IF(ISBLANK(TestData[Target]),VAR _curRank = TestData[Index]VAR _lastNonBlankRank = MAXX(FILTER(TestData, TestData[Index]< _curRank && NOT ISBLANK(TestData[Target]) ), TestData[Index])VAR _lastNonBlankValue = LOOKUPVALUE(TestData[Target], TestData[Index], _lastNonBlankRank)RETURN_lastNonBlankValue * POWER(1-0.3863636363,_curRank - _lastNonBlankRank),TestData[Target])
2 Replies
- wdx223_DanielCommunity Championgrowth_Target =IF(ISBLANK(TestData[Target]),VAR _curRank = TestData[Index]VAR _lastNonBlankRank = MAXX(FILTER(TestData, TestData[Index]< _curRank && NOT ISBLANK(TestData[Target]) ), TestData[Index])VAR _lastNonBlankValue = LOOKUPVALUE(TestData[Target], TestData[Index], _lastNonBlankRank)RETURN_lastNonBlankValue * POWER(1-0.3863636363,_curRank - _lastNonBlankRank),TestData[Target])
- snarayaHelper II
Thankyou so much 🙂