Every Excel Power Pivot Model or Power BI Desktop file needs a Calendar.
With the addition of a Calendar to your Data Model you can start to do all sorts of useful analysis such sorting the data by Fiscal month and Fiscal Year or performing calculations such as TotalYTD, Year On Year Growth, Actual v Full Year Budget etc... this list goes on.
There are several ways to create this Calendar, but the most flexible way is to use Power Query / aka Get & Transform / aka Get Data.
This does involve a little coding - hence the reference to "M" - which is the language of Power Query. Or, you'll be glad to hear, just some simple copy pasting!
Once you've taken 2 minutes to set it up you can re-use it again and again.
let
EndFiscalYearMonth = 6, //set this as the last month number of your fiscal year : June = 6, July =7 etc
StartDate= #date(2016,2,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
FYNameIf you're not familiar with M code then the // is a way of adding comments or "turning off" bits of code
I've set it up so that it can be used in a number of ways, but as it stands we have a StartDate of 1st Feb 2016 and an end date of Today by using the DateTime.LocalNow() function.
However, you can amend the code to a fixed End Date (especially when you need to capture Forecast / Budget dates in the future)
Best to start with the 1st of your current or any prior fiscal year and end with the last day of your current or future fiscal year.
Dynamic Start and End Dates
Maybe you want the Start Date and End date to be dynamically derived from the earliest and latest date in a column in a specific table / tables.
In that case, you add a double slash // before the StartDate and EndDate in rows 2 and 3 then remove the // from the 4 lines below. Then replace the word Sales and Invoice Date with the relevant query (table) name and column name.
//TableName = Sales
//DateColumnName = "Invoice Date"
//StartDate = Record.Field ( Table.Min(TableName,"DateColumnName") ,"DateColumnName"),
//EndDate = Record.Field(Table.Max(TableName,"DateColumnName"),"DateColumnName"),
Hope you find it useful
