Power Query Calendar With FY Columns

Problem Statement:

Need a calendar in Power Query with Financial Year Staring from April, with Financial Month Number, Qtr Number, Qtr Start Date, Week number etc.

Solution:

Create a table in Power Query with Financial Year starting from 1-Apr. Here I have created two dates: StartDate and EndDate; in the beginning. These dates can come from M parameters.

 

Code

 

let
		StartDate = Date.StartOfYear(Date.From(DateTime.FixedLocalNow())) ,
		EndDate =  Date.EndOfYear(Date.AddYears( Date.From(DateTime.FixedLocalNow()),2)), 
		Source = List.Dates( StartDate,
						Duration.Days( EndDate- StartDate) +1, 
						#duration(1,0,0,0) ),
		#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
		#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
		#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
		#"Added Custom" = Table.AddColumn(#"Changed Type", "StartOfMonth", each Date.StartOfMonth([Date])),
		#"Added Custom36" = Table.AddColumn(#"Added Custom", "EndOfMonth", each Date.EndOfMonth([Date])),
		#"Added Custom26" = Table.AddColumn(#"Added Custom36", "Month Year", each Date.ToText([Date],"MMM-yyyy")),
		#"Added Custom27" = Table.AddColumn(#"Added Custom26", "Month Year Sort", each Date.Year([Date])*100 +Date.Month([Date])),
		#"Added Custom1" = Table.AddColumn(#"Added Custom27", "StartOfWeek", each Date.StartOfWeek([Date],1)),
		#"Added Custom35" = Table.AddColumn(#"Added Custom1", "End of Week", each Date.EndOfWeek([Date],1)),
		#"Added Custom2" = Table.AddColumn(#"Added Custom35", "StartOfYear", each Date.StartOfYear([Date])),
		#"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndOfYear", each Date.EndOfYear([Date])),
		#"Added Custom31" = Table.AddColumn(#"Added Custom3", "Start of Qtr", each Date.StartOfQuarter([Date])),
		#"Added Custom32" = Table.AddColumn(#"Added Custom31", "End Of Qtr", each Date.EndOfQuarter([Date])),
		#"Added Custom33" = Table.AddColumn(#"Added Custom32", "Qtr Year", each Text.Combine({"Q", Number.ToText(Date.QuarterOfYear([Date])), "-",Number.ToText(Date.Year([Date]))})),
		#"Added Custom34" = Table.AddColumn(#"Added Custom33", "Year Qtr", each Date.Year([Date])*10 + Date.QuarterOfYear([Date])),
		#"Added Custom4" = Table.AddColumn(#"Added Custom34", "Day of Week (Tue)", each Date.DayOfWeek([Date],2)+1),
		#"Added Custom5" = Table.AddColumn(#"Added Custom4", "DayOfWeekName", each Date.DayOfWeekName([Date])),
		#"Added Custom6" = Table.AddColumn(#"Added Custom5", "DayOfYear", each Date.DayOfYear([Date])),
		#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Add days", each Date.AddDays([Date],-3)),
		#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Add Months", each Date.AddMonths([Date],2)),
		#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Current Time", each DateTime.LocalNow()),
		#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Fixed Local Now", each DateTimeZone.FixedLocalNow()),
		#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Todays date", each DateTime.Date(   DateTime.FixedLocalNow())),
		#"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is In Current Day", each if Date.IsInCurrentDay([Date]) then "Today" else Date.ToText([Date])),
		#"Added Custom13" = Table.AddColumn(#"Added Custom12", "Is Current Month", each Date.IsInCurrentMonth([Date])),
		#"Added Custom14" = Table.AddColumn(#"Added Custom13", "Month Type", each if Date.IsInCurrentMonth([Date]) then  "This Month" 
		else if Date.IsInNextMonth([Date]) then "Next Month"
		else if Date.IsInPreviousMonth([Date]) then "Last Month" else Date.ToText([Date], "MMM-yyyy")),
		#"Added Custom16" = Table.AddColumn(#"Added Custom14", "FY Start", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) -1,4,1) else #date(Date.Year([Date]) ,4,1)),
		#"Added Custom17" = Table.AddColumn(#"Added Custom16", "Fy End", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) ,3,31) else #date(Date.Year([Date])+1 ,3,31)),
		#"Added Custom18" = Table.AddColumn(#"Added Custom17", "FY Day of Year", each Duration.Days([Date] -[FY Start])+1),
		#"Added Custom20" = Table.AddColumn(#"Added Custom18", "Fy Month", each if Date.Month([Date])>=4 then
		Date.Month([Date])-Date.Month([FY Start])+1
		else Date.Month([Date])-Date.Month([FY Start])+1+12),
		#"Added Custom21" = Table.AddColumn(#"Added Custom20", "WeekofYear", each Date.WeekOfYear([Date])),
		#"Added Custom22" = Table.AddColumn(#"Added Custom21", "YearWeek", each Date.Year([Date]) *100 +Date.WeekOfYear([Date])),
		#"Added Custom15" = Table.AddColumn(#"Added Custom22", "FY Week Year Start", each Date.StartOfWeek([FY Start],Day.Monday)),
		#"Added Custom19" = Table.AddColumn(#"Added Custom15", "FY Week", each Number.IntegerDivide( Duration.Days([Date] -[FY Week Year Start]),7)+1),
		#"Added Custom23" = Table.AddColumn(#"Added Custom19", "FY Year Week", each Date.Year([FY Start])*100 + [FY Week]),
		#"Added Custom24" = Table.AddColumn(#"Added Custom23", "FY Qtr No", each Number.IntegerDivide([Fy Month] -1,3)+1),
		#"Added Custom25" = Table.AddColumn(#"Added Custom24", "FY Qtr Start Date", each Date.AddMonths([FY Start], Number.IntegerDivide([Fy Month]-1,3)*3)),
		#"Added Custom28" = Table.AddColumn(#"Added Custom25", "FY End of Qtr", each Date.AddDays( Date.AddMonths([FY Start], (Number.IntegerDivide([Fy Month]-1,3)+1)*3),-1)),
		#"Added Custom29" = Table.AddColumn(#"Added Custom28", "FY Qtr", each Date.Year([FY Start])*100 +[FY Qtr No]),
		#"Added Custom30" = Table.AddColumn(#"Added Custom29", "Qtr FY", each Text.Combine({"Q", Number.ToText([FY Qtr No]), "-",Number.ToText(Date.Year([FY Start]))})),
		#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom30",{{"StartOfWeek", type date}, {"End of Week", type date}, {"StartOfYear", type date}, {"EndOfYear", type date}, {"Start of Qtr", type date}, {"End Of Qtr", type date}, {"Add days", type date}, {"Add Months", type date}, {"FY Start", type date}, {"Fy End", type date}, {"FY Week Year Start", type date}, {"FY Qtr Start Date", type date}, {"FY End of Qtr", type date}, {"StartOfMonth", type date}, {"EndOfMonth", type date}, {"Current Time", type datetime}, {"Fixed Local Now", type datetime}})
in
#"Changed Type1"

 

 

Let me know if you want be add more columns