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
