Power Query to Get Fiscal Year & Fiscal Month

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,214
Office Version
  1. 365
Platform
  1. Windows
My fiscal year is 10/1 - 9/30 and I would like to modify the following M code so that it reflect the fiscal month and year correctly. Can someone help? So October 2018 should be fiscal month 1, January 2019 should be fiscal month 4, July 2019 should be fiscal month 10, etc.


Code:
let
   

    EndFiscalYearMonth = 9,   //set this as the last month number of your fiscal year : June = 6, July =7 etc


    StartDate= #date(2018, 10, 1),     // Change start date  #date(yyyy,m,d)   
    EndDate = DateTime.LocalNow(),  // Could change to #date(yyyy,m,d) if you need to specify future date


/* Comment out the above StartDate and EndDate using // if you want to use a dynamic start and end date based on other query/table
   You will need to change "Sales" and "Invoice Date" in 2 lines below and then remove the // 
*/


    //TableName = Sales    
    //DateColumnName = "Invoice Date"
    //StartDate = Record.Field (   Table.Min(TableName,DateColumnName)  ,DateColumnName), 
    //EndDate = Record.Field(Table.Max(TableName,DateColumnName),DateColumnName),


    
    DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Calendar Year", each Date.Year([Date]), type number),
    #"Inserted Month Number" = Table.AddColumn(#"Inserted Year", "Month Number", each Date.Month([Date]), type number),
    #"Long Month Name" = Table.AddColumn(#"Inserted Month Number", "Month Long", each Date.MonthName([Date]), type text),
    #"Short Month Name" = Table.AddColumn(#"Long Month Name", "Month", each Text.Start([Month Long], 3), type text),
    #"Fiscal Month Number" = Table.AddColumn(#"Short Month Name", "Fiscal Month Number", each if [Month Number] > EndFiscalYearMonth  then [Month Number]-EndFiscalYearMonth  else [Month Number]+EndFiscalYearMonth),
    #"Changed Type1" = Table.TransformColumnTypes(#"Fiscal Month Number",{{"Fiscal Month Number", Int64.Type}}),
    #"Fiscal Year" = Table.AddColumn(#"Changed Type1", "Fiscal Year", each if [Fiscal Month Number] <=EndFiscalYearMonth  then [Calendar Year]+1 else [Calendar Year]),
    #"Changed Years to Text" = Table.TransformColumnTypes(#"Fiscal Year",{{"Fiscal Year", type text}, {"Calendar Year", type text}}),
    FYName = Table.AddColumn(#"Changed Years to Text", "FYName", each "FY"&Text.End([Fiscal Year],2))
in
    FYName
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Thanks but that doesn't show how to get the fiscal month and fiscal year.
 
Upvote 0
there is Fin Year and Fin Month (Financial = Fiscal)

Ah! Didn't read that. I got the fiscal year correct but I'm having trouble with the following part to get the correct fiscal month


Code:
= Table.AddColumn(#"Inserted Day Name", "Fiscal Month", each if Date.Month([Date]) >=10 then Date.Month([Date])-9 else Date.Month([Date])+9)


My fiscal month starts Oct and ends in Sept, thus it should result in the following:
Oct - 1
Nov - 2
Dec - 3
Jan - 4
Feb - 5
....
....
Sept - 12
 
Upvote 0
Was able to figure it out by changing it to the following. Thanks again!

= Table.AddColumn(#"Inserted Day Name", "Fiscal Month", each if Date.Month([Date]) >=10 then Date.Month([Date])-9 else Date.Month([Date])+3)
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top