Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I'm working on an expression that calculates the accumulative variance
the main expression gets values up to April 2023 (limited in set analysis)
then I used Range Sum to calculate the variance
then I chose Accumulation in the modifier to calculate the accumulative variance
the numbers are correct but I want to display values up to 2023 04
main Expression is :
Sum({<MonthDiff={0},Scenario={Actual},Date_Num={"<$(=Max({1<[Is Current Month]={1}>}[Date_Num]))"}>}Periodic)
the expression that calculates the variance is :
fabs
(rangesum(
Sum( {<MonthDiff={0},Scenario={Actual},Date_Num={"<$(=Max({1<[Is Current Month]={1}>}[Date_Num])-1)"}>}Periodic )
,
-1*above( Sum( {<[Year Month]=, MonthDiff={0},Scenario={Actual},Date_Num={"<$(=Max({1<[Is Current Month]={1}>}[Date_Num])-1)"}>}Periodic ))*avg(1)
)
kindly advise
You may just use your main-expression as a boolean check, like:
VarianceExpression * sign(MainExpression)
You may just use your main-expression as a boolean check, like:
VarianceExpression * sign(MainExpression)
may you please elaborate more?
It's not complicated. Your fabs(rangesum(above(...))) creates future-results for not existing values just repeating the last result for all further exists dimension-values. In general it should be possible to add further condition within this variance-expression but it won't be trivial. Much easier is to put a boolean check on the outside. This means for all existing data your main-expression will return a (positive) value and sign() returns as a wrapper 1 and the not existing data results in 0 and sign returned 0 which means in the end that your variance-expression is multiplied with 1 respectively 0 and the zero-results could be hidden per layout-option.
Beside using the MainExpression as check you might be also just add a 0/1 flag within the calendar related to dates <= monthend/montstart of today() respectively are >= and then using the flag as factor or as further dimension/expression-condition.