legalhustler
Well-known Member
- Joined
- Jun 5, 2014
- Messages
- 1,214
- Office Version
- 365
- Platform
- 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