Power Query to Get Fiscal Year & Fiscal Month

[legalhustler]

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.

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

 

[sandy666]

maybe it will help: Build Calendar

 

[legalhustler]

Thanks but that doesn't show how to get the fiscal month and fiscal year.

 

[sandy666]

there is Fin Year and Fin Month (Financial = Fiscal)

 

[legalhustler]

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

= 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

 

[legalhustler]

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)

 

[sandy666]

you are welcome and have a nice day