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?
