Hello folks,
First of all, I'd like to say I am extremely new to this community, as a matter of fact this is my very first post and if that’s not enough, I am also relatively new to Power BI, so please bear with me. 🙂
Anyway, the challenge I am facing is that while I am modelling my data, I have to play around with one of the “Date” fields in order to sort the records until able to group them by the Fiscal Year which begins on 1st of Sept each year and end on the 31st of August the following year (e.g. FY18 is 01/09/2017 to 31/08/2018).
Here is the formula which allows me to achieve the above:
Fiscal Year = IF(AND([Year] =2009,[Month]<9),"FY09",
IF(AND([Year] =2009,[Month]>=9),"FY10",
IF(AND([Year] =2010,[Month]<9),"FY10",
IF(AND([Year] =2010,[Month]>=9),"FY11",
IF(AND([Year] =2011,[Month]<9),"FY11",
.
.
.
IF(AND([Year] =2017,[Month]>=9),"FY18",
IF(AND([Year] =2018,[Month]<9),"FY18",
IF(AND([Year] =2018,[Month]>=9),"FY19",
IF(AND([Year] =2019,[Month]<9),"FY19",
" ")))))))))))))))))))))
The formula works fine, however, it is labour intensive because I end up setting up the parameter for each FY manually, so the question is, can the above be written in a more automated way?
Looking forward to your replys.
Thank you.
