Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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
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])
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!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
7 | |
7 | |
6 | |
6 |