Forum Discussion
Custom Column - Current Fiscal Year
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?
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 FredesDid 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
3 Replies
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 FredesDid 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
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])
