Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Unknowncharacte
Helper III
Helper III

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 DateFiscal Year
09/29/20242024
09/30/20242024
10/01/20242025
10/02/20242025

 

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

Calendar DateCalendar YearFiscal YearMonth NameMonth Number
10/01/202420242025October10

 

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?

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1720678166481.png

 

vxinruzhumsft_1-1720678179996.png

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

 

 

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1720678166481.png

 

vxinruzhumsft_1-1720678179996.png

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

AlanFredes
Resolver IV
Resolver IV

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors