Fiscal Year Formula in Set Analysis

[mskusace]

I have a set of KPIs for each Month in our Fiscal Year (OCT - SEP). The KPI is pulling from a field called "MONTH" which is 'yyyymm' and each KPI has it hard coded as '201910' for OCT Fiscal Year 2020, '201911' for NOV Fiscal Year 2020, etc.

I am trying to make the formula more dynamic to auto detect the fiscal year. I tried the formula below, but I get an error in the expression.

If Month >=10  THEN Fiscal Year = Current Year + 1 ELSE Fiscal Year = Current Year. Then concatenate the left 4 of the fiscal year with the hard coded MONTH value, which can remain static. 

IF(SUM({$<[MONTH]={IF(Month(Date(Today(),'M/D/YYYY h:mm:ss[.fff] TT'))>=10,RIGHT(Year(Date(Today()))+1,4),RIGHT(Year(Date(Today())),4))&'10'}>}[PLANNED_AMT])='0','-',SUM({$<[MONTH]={IF(Month(Date(Today(),'M/D/YYYY h:mm:ss[.fff] TT'))>=10,RIGHT(Year(Date(Today()))+1,4),RIGHT(Year(Date(Today())),4))&'10'}>}[PLANNED_AMT]))

How can I make this work or is there a better solution?

[sunny_talwar · Accepted solution]

For Jan, may be change it to this

If(Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1, Year(Today())) & 01)"}>} [PLANNED_AMT]) = 0,
'-',
Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1, Year(Today())) & 01)"}>} [PLANNED_AMT])
)

[mskusace]

I really appreciate the assistance! I totally missed that. I did not know I could pass text like that from a formula. Thank you so much!