Custom Column - Current Fiscal Year

Hi Unknowncharacte 

This if statement in a custom column should be able to give you what you want.

The logic tries to identify if you are within the month range and within the Current Fiscal Year.

if Date.Month([Calendar Date])>=10 and Date.Month([Calendar Date])<=12 and [Fiscal Year] = Date.Year(DateTime.LocalNow())+1 then "Y"
else if Date.Month([Calendar Date])>=1 and Date.Month([Calendar Date])<=9 and [Fiscal Year] = Date.Year(DateTime.LocalNow()) then "Y"
else "N"

Regards,
Alan Fredes

Did I answer your question? Mark my post as a solution!

Hi,

Thanks for the solution AlanFredes  provided, and i want to offer some more infotmation for user to refer to.

hello Unknowncharacte , you can create a the following custom column.

=let
  currentdate = DateTime.Date(DateTime.LocalNow()), 
  currentyear = Date.Year(currentdate), 
  lastyear = currentyear - 1, 
  nextyear = currentyear + 1, 
  currentFY = 
    if currentdate >= #date(lastyear, 10, 1) and currentdate <= #date(currentyear, 9, 30) then
      currentyear
    else if currentdate >= #date(currentyear, 10, 1) and currentdate <= #date(nextyear, 9, 30) then
      nextyear
    else
      0
in
  if [Fiscal Year] = currentFY then "Current Fiscal Year" else "FY" & Number.ToText([Fiscal Year])

And you can refer to the following m code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUNwIiAyMTJR0lZCpWByxpbIBL0tBA38AQU9IULmmEVTIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calendar Date" = _t, #"Calendar Year" = _t, #"Fiscal Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Calendar Date", type date}, {"Calendar Year", Int64.Type}, {"Fiscal Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Current Fiscal Year", each let
  currentdate = DateTime.Date(DateTime.LocalNow()), 
  currentyear = Date.Year(currentdate), 
  lastyear = currentyear - 1, 
  nextyear = currentyear + 1, 
  currentFY = 
    if currentdate >= #date(lastyear, 10, 1) and currentdate <= #date(currentyear, 9, 30) then
      currentyear
    else if currentdate >= #date(currentyear, 10, 1) and currentdate <= #date(nextyear, 9, 30) then
      nextyear
    else
      0
in
  if [Fiscal Year] = currentFY then "Current Fiscal Year" else "FY" & Number.ToText([Fiscal Year]))
in
    #"Added Custom"

Output