Cheat Sheet: Power Query Financial Year Calendar
6 min readAug 11, 2022
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: Created 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. Please find the Script for Microsoft Fabric Gen 2 after this script
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"Microsoft Fabric Gen 2 Date Table script.
let
StartDate = #date(2012,01,01) ,
EndDate = Date.EndOfYear(Date.AddYears( Date.From(DateTime.FixedLocalNow()),1)),
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 datetime}, {"End of Week", type datetime}, {"StartOfYear", type datetime}, {"EndOfYear", type datetime}, {"Start of Qtr", type datetime}, {"End Of Qtr", type datetime}, {"Add days", type datetime}, {"Add Months", type datetime}, {"FY Start", type datetime}, {"Fy End", type datetime}, {"FY Week Year Start", type datetime}, {"FY Qtr Start Date", type datetime}, {"FY End of Qtr", type datetime}, {"StartOfMonth", type datetime}, {"EndOfMonth", type datetime}, {"Current Time", type datetime}, {"Fixed Local Now", type datetime}, {"DayOfWeekName", type text}, {"DayOfYear", Int64.Type}, {"Is Current Month", type text}, {"Is In Current Day", type text}, {"Todays date", type text}, {"Month Type", type text}, {"FY Day of Year", Int64.Type}, {"Fy Month", Int64.Type}, {"WeekofYear", Int64.Type}, {"YearWeek", Int64.Type}, {"FY Week", Int64.Type}, {"FY Year Week", Int64.Type}, {"FY Qtr No", Int64.Type}, {"FY Qtr", Int64.Type}, {"Qtr FY", type text}, {"Day of Week (Tue)", Int64.Type}, {"Year Qtr", Int64.Type}, {"Qtr Year", type text}, {"Month Year Sort", Int64.Type}, {"Month Year", type text}, {"Date", type date}}),
#"Renamed columns 1" = Table.RenameColumns(#"Changed Type1", {{"Month Year", "MonthYear"}, {"Month Year Sort", "MonthYearSort"}, {"End of Week", "EndofWeek"}, {"Start of Qtr", "StartofQtr"}, {"End Of Qtr", "EndOfQtr"}, {"Qtr Year", "QtrYear"}, {"Year Qtr", "YearQtr"}, {"Day of Week (Tue)", "DayofWeekTue"}, {"Add days", "Adddays"}, {"Add Months", "AddMonths"}, {"Current Time", "CurrentTime"}, {"Fixed Local Now", "FixedLocalNow"}, {"Todays date", "Todaysdate"}, {"Is In Current Day", "IsInCurrentDay"}, {"Is Current Month", "IsCurrentMonth"}, {"Month Type", "MonthType"}, {"FY Start", "FYStart"}, {"Fy End", "FyEnd"}, {"FY Day of Year", "FYDayofYear"}, {"Fy Month", "FyMonth"}, {"FY Week Year Start", "FYWeekYearStart"}, {"FY Week", "FYWeek"}, {"FY Year Week", "FYYearWeek"}, {"FY Qtr No", "FYQtrNo"}, {"FY Qtr Start Date", "FYQtrStartDate"}, {"FY End of Qtr", "FYEndofQtr"}, {"FY Qtr", "FYQtr"}, {"Qtr FY", "QtrFY"}})
in
#"Renamed columns 1"Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.
You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share
