Lock a variable in line chart.

[sebHan]

Hey! I have a problem with locking a variable in my timeline.

Basically my line chart is based on the Fiscal year 21/22 (July 2021 - June 2022).

In this line chart, I have data up until  November.

Afterwards I would like to project future number of the figure, based on a quite simple formula.

The formula I have tried using is the following:

if([Historic PP] <> 0, [Historic PP]

,

RangeSum(Sum([FuturePP])/1000,0,RowNo())*

above(alt((Sum ({$1<[YTD]={'YTD'}>} [Historic PP])+Sum ({$1<[YTD]={'YTD'}>} [Planned V])
+Sum ({$<[YTD]={'YTD'}>} [Actual V]))*alt(1/Sum ({$1<[YTD]={'YTD'}>} [Historic PP]),1)))

+RangeSum(Sum ({1} {$<[YTD]={'YTD'}>} [Historic PP])/1000*

above(alt((Sum ({1} {$<[YTD]={'YTD'}>} [Historic PP])+Sum ({1} {$<[YTD]={'YTD'}>} [Planned V])
+Sum ({1} {$<[YTD]={'YTD'}>} [Actual V]))*alt(1/Sum ({1} {$<[YTD]={'YTD'}>} [Historic PP]),1)))

)

Where the first part of the if statement is working. However, the second part the variable [FuturePP] will vary from December 2021 till June 2022, and the other part of the formula is varying from June 2021 til November 2021.

In this formula, I would like to use the calculated value at November 2021, and then have the same constant timed on [FuturePP] from December 2021-June 2022.

To elaborate the last part of the if/else statement works if I hardcode the constant (example below)

RangeSum(Sum([FuturePP])/1000,0,RowNo())*

0.5

+RangeSum(Sum ({1} {$<[YTD]={'YTD'}>} [Historic PP])/1000*

0.5

Can anybody help here?