Forum Discussion

snaraya's avatar
snaraya
Helper II
3 years ago
Solved

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= ...
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    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])
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago
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])
snaraya's avatar
snaraya
Helper II
3 years ago

Thankyou so much ðŸ™‚