Forum Discussion
Customize fiscal year
Hi all!
I built the Fiscal Year starting on sept 1st and finishing on oct 30th. Here is the calculated field that I created and till a few days ago, it was working fine:
Fiscal Year = IF(AND([Year] =2012,[Month]<=10),"FY12-13",
IF(AND([Year] =2013,[Month]<=9),"FY13-14",
IF(AND([Year] =2013,[Month]>=10),"FY13-14",
IF(AND([Year] =2014,[Month]<=9),"FY13-14",
IF(AND([Year] =2014,[Month]>=10),"FY14-15",
IF(AND([Year] =2015,[Month]<=9),"FY14-15",
IF(AND([Year] =2015,[Month]>=10),"FY15-16",
IF(AND([Year] =2016,[Month]<=9),"FY15-16",
IF(AND([Year] =2016,[Month]>=10),"FY16-17",
IF(AND([Year] =2017,[Month]<=9),"FY16-17",
IF(AND([Year] =2017,[Month]>=10),"FY17-18",
IF(AND([Year] =2018,[Month]<=9),"FY17-18",
IF(AND([Year] =2018,[Month]>=10),"FY18-19",
IF(AND([Year] =2019,[Month]<=9),"FY18-19",
IF(AND([Year] =2019,[Month]>=10),"FY19-20",
IF(AND([Year] =2020,[Month]<=9),"FY19-20",
IF(AND([Year] =2020,[Month]>=10),"FY20-21",
IF(AND([Year] =2021,[Month]<=9),"FY20-21",
IF(AND([Year] =2021,[Month]>=10),"FY21-22",
IF(AND([Year] =2022,[Month]<=9),"FY21-22",
" "))))))))))))))))))))
Now, I´m getting this error "The syntax for '[Month]' is incorrect. (DAX(IF(AND([Year] =2012.[Month]<=10),"FY12-13",IF(AND([Year] =2013.[Month]<=9),"FY13-14",IF(AND([Year] =2013.[Month]>=10),"FY13-14", IF(AND([Year] =2014.[Month]<=9),"FY13-14",IF(AND([Year] =2014.[Month]>=10),"FY14-15",IF(AND([Year] =2015.[Month]<=9),"FY14-15",IF(AND([Year] =2015.[Month]>=10),"FY15-16",IF(AND([Year] =2016.[Month]<=9),"FY15-16",IF(AND([Year] =2016.[Month]>=10),"FY16-17",IF(AND([Year] =2017.[Month]<=9),"FY16-17",IF(AND([Year] =2017.[Month]>=10),"FY17-18",IF(AND([Year] =2018.[Month]<=9),"FY17-18",IF(AND([Year] =2018.[Month]>=10),"FY18-19",IF(AND([Year] =2019.[Month]<=9),"FY18-19",IF(AND([Year] =2019.[Month]>=10),"FY19-20",IF(AND([Year] =2020.[Month]<=9),"FY19-20",IF(AND([Year] =2020.[Month]>=10),"FY20-21",IF(AND([Year] =2021.[Month]<=9),"FY20-21",IF(AND([Year] =2021.[Month]>=10),"FY21-22",IF(AND([Year] =2022.[Month]<=9),"FY21-22"," "))))))))))))))))))))))"
Do you know why is this happening and how to solve it please?
Thanks!
JessicaM ,
Here's a long-term solution to dynamically generate this field, for fiscal year starting September 1st:
_fisYear = IF( MONTH(Calendar[date]) <= 8, "FY" & RIGHT(YEAR(Calendar[date]) - 1, 2) & "-" & RIGHT(YEAR(Calendar[date]), 2), "FY" & RIGHT(YEAR(Calendar[date]), 2) & "-" & RIGHT(YEAR(Calendar[date]) + 1, 2) )Pete
7 Replies
Hi JessicaM ,
From your error message, it looks like there are full stops (periods/dots) in between the AND() arguments, rather than commas:
This would confuse Power BI. Oddly, this doesn't correspond with the calculated field code you posted previously.
Can you check that the actual field code doesn't have these full stops?
Pete
JessicaM ,
Here's a long-term solution to dynamically generate this field, for fiscal year starting September 1st:
_fisYear = IF( MONTH(Calendar[date]) <= 8, "FY" & RIGHT(YEAR(Calendar[date]) - 1, 2) & "-" & RIGHT(YEAR(Calendar[date]), 2), "FY" & RIGHT(YEAR(Calendar[date]), 2) & "-" & RIGHT(YEAR(Calendar[date]) + 1, 2) )Pete
Thanks a lot for your answer but still not working. I have the same issue with dots and commas... check out this error. Within the code is not appearing but I have it in the error message 😞
That is correct. The code above is not the same like the error message but is exactly what I have in my screen... please see the screenshot:
