Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JessicaM
Frequent Visitor

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!

1 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 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
BA_Pete
Super User
Super User

Hi @JessicaM ,

 

From your error message, it looks like there are full stops (periods/dots) in between the AND() arguments, rather than commas:

BA_Pete_0-1615478396754.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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:

JessicaM_0-1615479907029.png

 

@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 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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 😞

JessicaM_1-1615480124002.png

 

@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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks Pete, 

 

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

 

Thank you!!

@JessicaM ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.