help with expression with dynamic variables

[ashmitp869]

Hi there,

Can anyone help me with the expression - I want to compare values of CurrentFY and PreviousFY.

Sum({<[Departure Date.FinancialYear]={">=vPreviousFY<=vCurrentFY"}>}[ToC Numerator])/Sum({<[Departure Date.FinancialYear]={">=vPreviousFY<=vCurrentFY"}>}[ToC Denominator])

NB: vCurrentFY =

=If(Num(Month(Today())-1)>6,Year(Today())&'/'&Year(addmonths(Today(),12)),Year(Addmonths(Today(),-12))&'/'&Year(Today()))

value is 2022/2023

vPreviousFY =

=If(Num(Month(Today())-1)>6,Year(Addmonths(Today(),-12))&'/'&Year(addmonths(Today(),24)),Year(Addmonths(Today(),-24))&'/'&Year(Addmonths(Today(),-12)))

value is 2021/2022

[Gabbar]

try using :
$(=variable) or $(variable)
in set expression.

[jdurmus]

It looks like you are trying to compare values from the CurrentFY and PreviousFY for the given expression. However, the comparison expression in the set analysis is not correct.

The correct set analysis expression for comparing the values of CurrentFY and PreviousFY should be:

Sum({<[Departure Date.FinancialYear]={">=$(vPreviousFY)<=$(vCurrentFY)"}>}[ToC Numerator])/Sum({<[Departure Date.FinancialYear]={">=$(vPreviousFY)<=$(vCurrentFY)"}>}[ToC Denominator])

Here, we have replaced the "<=" operator with "<= $ " and ">= $ " which is the correct syntax for comparing values with variables in set analysis. Also, we have swapped the order of CurrentFY and PreviousFY in the comparison operator, as we want to select the data where the FinancialYear is between vPreviousFY and vCurrentFY.

Please try this updated expression and let me know if it works for you.