PQ Dinamyc Calendar

Hello Anonymous 

use this code instead

let CreateDateTable = (StartDate as date, optional Culture as nullable text) as table =>
let
EndDate = Date.From(Date.EndOfMonth(DateTime.FixedLocalNow())) /*Date.AddDays(DateTime.Date(DateTime.LocalNow()),1)*/,

Culture = if Culture = null then "en-US" else Culture ,

DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterNumOfYear", each Date.QuarterOfYear([Date])),
insertQuarterName = Table.AddColumn(InsertQuarter , "QuarterName" , each "Q-" & Number.ToText([QuarterNumOfYear])),
insertHalfOfYearNum = Table.AddColumn(insertQuarterName , "HalfOfYearNum", each Number.RoundDown(([QuarterNumOfYear]+1)/2), type number),
insertHalfOfYearName = Table.AddColumn(insertHalfOfYearNum ,"HalfOfYearName",each "H-" & Number.ToText([HalfOfYearNum])),
insertHalfOfCalendarYear = Table.AddColumn(insertHalfOfYearName ,"HalfOfCalendarYear", each "H" & Number.ToText([HalfOfYearNum]) & " " & Number.ToText([Year])),
insertHalfOfCalendarYearSum = Table.AddColumn( insertHalfOfCalendarYear ,"HalfOfCalendarYearSum",each [Year]*10 +[HalfOfYearNum], type number),
InsertMonth = Table.AddColumn(insertHalfOfCalendarYearSum , "MonthNumberOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthNumberOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendarName", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterNumOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayOFWeekNum", each 1+Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Trim(Text.Replace(Date.ToText([Date], "dddd", Culture),"éåí","")," "), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertYearMonthSum = Table.AddColumn(InsertWeekEnding,"YearMonthSum", each [Year]*100 +[MonthNumberOfYear]),
InsertYearQuarterSum = Table.AddColumn(InsertYearMonthSum ,"YearQuarterSum", each [Year]*10 +[QuarterNumOfYear], type number),
insertDaySelection = Table.AddColumn(InsertYearQuarterSum , "Show Dates", each (if Date.IsInCurrentDay(Date.AddDays([Date],1)) then "Yesterday" else (if Date.IsInCurrentDay([Date]) then "Today" else "Other Dates"))),
insertMonthSelection = Table.AddColumn(insertDaySelection , "Show Months", each (if Date.IsInPreviousMonth([Date]) then "Last Month" else (if Date.IsInCurrentMonth([Date]) then "This Month" else "Other Dates")))
in
insertMonthSelection,
#"Invoked FunctionCreateDateTable" = CreateDateTable(Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.FixedLocalNow(), -4))), "en-US"),
#"Changed Type" = Table.TransformColumnTypes(#"Invoked FunctionCreateDateTable",{{"MonthNumberOfYear", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"QuarterInCalendar", "Quarter"}, {"MonthInCalendarName", "Month"}}),
#"Added Custom-MonthRelative" = Table.AddColumn(#"Renamed Columns", "MonthRelative", each if Date.IsInCurrentMonth([Date]) then "Current Month" else
if Date.IsInPreviousMonth([Date]) then "Last Month" else
[Month]),
#"Added Custom1-YearRelative" = Table.AddColumn(#"Added Custom-MonthRelative", "YearRelative", each if Date.IsInCurrentYear([Date]) then "Current Year" else
if Date.IsInPreviousYear([Date]) then "Last Year" else
[Year])
in
#"Added Custom1-YearRelative"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy