Welcome to powertothemasses.com.
M-code for Calendar:
let Source = List.Dates(Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))-4,1,1,0,0,0)), Duration.Days(Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))+10,1,1,0,0,0))-Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))-4,1,1,0,0,0))), #duration(1, 0, 0, 0)), #"Converted to Table1" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month", each Date.MonthName([Date]), type text), #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month", each Text.Start(_, 3), type text}}), #"Inserted Month" = Table.AddColumn(#"Extracted First Characters", "Month#", each Date.Month([Date]), Int64.Type), #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type), #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day of Week", "Week of Year", each if [Day of Week] = 7 then Date.WeekOfYear([Date]) + 1 else Date.WeekOfYear([Date])), #"Added Custom" = Table.AddColumn(#"Inserted Week of Year", "Yearmonth", each [Year]*100+[#"Month#"]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Yearmonth", Int64.Type}}), Reference1 = Table.ReorderColumns(#"Changed Type1",{"Date", "Year", "Month", "Month#", "Yearmonth", "Day of Week", "Week of Year"}), #"Removed Other Columns" = Table.SelectColumns(Reference1,{"Yearmonth"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "YMIndex", 0, 1), #"Merged Queries" = Table.NestedJoin(Reference1,{"Yearmonth"},#"Added Index",{"Yearmonth"},"Added Index",JoinKind.LeftOuter), #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"YMIndex"}, {"YMIndex"}), #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Added Index",{{"YMIndex", Int64.Type}}), #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type2",{"Date", "Year", "Month", "Month#", "Yearmonth", "YMIndex", "Day of Week", "Week of Year"}), #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "Year", "Year - Copy"), #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Year - Copy", type text}}, "en-AU"), "Year - Copy", Splitter.SplitTextByPositions({0, 2}, false), {"Year - Copy.1", "Year - Copy.2"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position",{{"Year - Copy.1", Int64.Type}, {"Year - Copy.2", Int64.Type}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Year - Copy.2", "Shortyear"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [#"Month#"] <=6 then [#"Shortyear"] - 1 else [#"Shortyear"]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each if [#"Month#"] >=7 then [Shortyear] +1 else [Shortyear]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Shortyear"}), #"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.1", type text}, {"Custom", type text}, {"Year - Copy.1", type text}}), #"Added Custom3" = Table.AddColumn(#"Changed Type4", "FinYear", each [#"Year - Copy.1"]&[Custom]&"/"&[Custom.1]), #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"FinYear", type text}}), Reference2 = Table.RemoveColumns(#"Changed Type5",{"Year - Copy.1", "Custom", "Custom.1"}), #"Removed Other Columns1" = Table.SelectColumns(Reference2,{"FinYear"}), #"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"), #"Added Index1" = Table.AddIndexColumn(#"Removed Duplicates1", "FYIndex", 0, 1), #"Merged Queries1" = Table.NestedJoin(#"Reference2",{"FinYear"},#"Added Index1",{"FinYear"},"Added Index1",JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index1", {"FYIndex"}, {"FYIndex"}), #"Inserted End of Week" = Table.AddColumn(#"Expanded Added Index1", "Previous Friday", each Date.AddDays(Date.EndOfWeek([Date]),-2-7), type date), #"Duplicated Column1" = Table.DuplicateColumn(#"Inserted End of Week", "Month", "FinMonth"), #"Added Custom4" = Table.AddColumn(#"Duplicated Column1", "FinMonth#", each if [#"Month#"] >= 7 then [#"Month#"] -6 else [#"Month#"] +6), #"Changed Type6" = Table.TransformColumnTypes(#"Added Custom4",{{"FinMonth#", Int64.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type6",{"Date", "Year", "Month", "Month#", "Yearmonth", "YMIndex", "Day of Week", "Week of Year", "FinYear", "FinMonth", "FinMonth#", "FYIndex", "Previous Friday"}), #"Inserted Quarter" = Table.AddColumn(#"Reordered Columns", "YQtr", each Date.QuarterOfYear([Date]), Int64.Type), #"Added Custom5" = Table.AddColumn(#"Inserted Quarter", "Custom", each [Year]*10+[YQtr]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"YQtr"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "YearQtr"}}), Reference3 = Table.TransformColumnTypes(#"Renamed Columns2",{{"YearQtr", Int64.Type}}), #"Removed Other Columns2" = Table.SelectColumns(Reference3,{"YearQtr"}), #"Removed Duplicates2" = Table.Distinct(#"Removed Other Columns2"), #"Added Index2" = Table.AddIndexColumn(#"Removed Duplicates2", "YQIndex", 0, 1), #"Merged Queries2" = Table.NestedJoin(#"Reference3",{"YearQtr"},#"Added Index2",{"YearQtr"},"Reference3",JoinKind.LeftOuter), #"Expanded Reference3" = Table.ExpandTableColumn(#"Merged Queries2", "Reference3", {"YQIndex"}, {"YQIndex"}), #"Renamed Columns3" = Table.RenameColumns(#"Expanded Reference3",{{"Week of Year", "Week#"}}), #"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Week#", Int64.Type}}) in #"Changed Type7"
Sign up to become a member of the Hobart Power BI User Group HERE.
Enjoy building your Power BI model!