Custom Column - Current Fiscal Year

[Unknowncharacte]

Hello, 

I am trying to identify current FISCAL year. My fiscal year starts on October 1st and ends on September 30th, for example:

Calendar Date	Fiscal Year
09/29/2024	2024
09/30/2024	2024
10/01/2024	2025
10/02/2024	2025

I have a calendar table in which I want to add a column identifying current FISCAL year, here are my columns

Calendar Date	Calendar Year	Fiscal Year	Month Name	Month Number
10/01/2024	2024	2025	October	10

I was able to identify current CALENDAR year by creating a custom colum with this syntax:

if Date.Year(DateTime.LocalNow()) = [Calendar Year]

then "Current Year"

else "FY"&Number.ToText([Calendar Year]) 

However, I cannot figure out how to achive the same but for the FISCAL Year, it just doesn't compute in my mind. Can anyone help please?

[AlanFredes · Accepted solution]

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!

[v-xinruzhu-msft · Accepted 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

[Unknowncharacte]

Thank you, and if I need to adjust this to last fiscal year, do I just change "lastyear = currentyear - 2"?

=let

  currentdate = DateTime.Date(DateTime.LocalNow()),

  currentyear = Date.Year(currentdate),

  lastyear = currentyear - 2,

  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])

[AlanFredes · Accepted solution]

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!