Customize fiscal year

[JessicaM]

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!

[BA_Pete]

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]

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:

[BA_Pete · Accepted solution]

@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 

[JessicaM]

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 😞

[BA_Pete]

@JessicaM ,

I've just read up on this and it seems it may be a regional settings issue.

Another user has a similar issue here: PBI forum 

Check what settings you are using and make sure it's set to a region that uses commas for code delimiters rather than full stops or semi-colons.

Be mindful that some of the regional settings will only take effect when new PBIX files are created, and cannot be changed on existing files.

Maybe also try this code instead to see what happens:

fisYear =
IF(
    MONTH(cal[date]) <= 8;
    "FY" & RIGHT(YEAR(cal[date]) - 1; 2) & "-" & RIGHT(YEAR(cal[date]); 2);
    "FY" & RIGHT(YEAR(cal[date]); 2) & "-" & RIGHT(YEAR(cal[date]) + 1; 2)
)

Pete

[JessicaM]

Thanks Pete, 

It was the regional settings. I just reset them and now it is working fine. 

Thank you!!

[BA_Pete]

@JessicaM ,

Are you using Direct Query or Live Connection to connect to your source?