Calendar Power BI

[jcnascime]

Hello,

I have this script, and I would like the period_namber and period_name columns to follow the same criteria as the week_namber column.

In this example, week 05 is returning periods 1 and 2, and the correct option for my case would be to return only week 01.
and on the first day of week 06 return P02

Consider Monday as the first day of the week. Is it possible to make corrections to meet these criteria?
Here is the script:

let
startDate = #date(2024, 1, 1),
endDate = #date(Date.Year(DateTime.LocalNow()), 12, 31),

numDays = Duration.From(endDate - startDate),
dateList = List.Dates(startDate, Number.From(numDays) + 1, #duration(1, 0, 0, 0)),
dateTable = Table.FromList(dateList, Splitter.SplitByNothing()),
renamedTable = Table.RenameColumns(dateTable, {{"Column1", "date_short"}}),

numDayColumn = Table.AddColumn(renamedTable, "num_day", each Date.Day([date_short]), Int64.Type),
numMonthColumn = Table.AddColumn(numDayColumn, "num_month", each Date.Month([date_short]), Int64.Type),
numYearColumn = Table.AddColumn(numMonthColumn, "num_year", each Date.Year([date_short]), Int64.Type),

nameMonthBrColumn = Table.AddColumn(numYearColumn, "name_month_br", each Date.ToText([date_short], "MMMM"), type text),
nameMonthUsColumn = Table.AddColumn(nameMonthBrColumn, "name_month_us", each Date.ToText([date_short], "MMMM", "en-US"), type text),
nameMonthShortBrColumn = Table.AddColumn(nameMonthUsColumn, "name_month_short_br", each Text.Start([name_month_br], 3), type text),
nameMonthShortUsColumn = Table.AddColumn(nameMonthShortBrColumn, "name_month_short_us", each Text.Start([name_month_us], 3), type text),

numWeekdayColumn = Table.AddColumn(nameMonthShortUsColumn, "num_weekday", each Date.DayOfWeek([date_short], Day.Monday), Int64.Type),
nameWeekdayShortBrColumn = Table.AddColumn(numWeekdayColumn, "name_weekday_short_br", each Text.Start(Date.ToText([date_short], "dddd"), 3), type text),
nameWeekdayBrColumn = Table.AddColumn(nameWeekdayShortBrColumn, "name_weekday_br", each Date.ToText([date_short], "dddd"), type text),
nameWeekdayShort2BrColumn = Table.AddColumn(nameWeekdayBrColumn, "name_weekday_short2_br", each Text.Start([name_weekday_br], 3), type text),
nameWeekdayUsColumn = Table.AddColumn(nameWeekdayShort2BrColumn, "name_weekday_us", each Date.ToText([date_short], "dddd", "en-US"), type text),
nameWeekdayShortUsColumn = Table.AddColumn(nameWeekdayUsColumn, "name_weekday_short_us", each Text.Start([name_weekday_us], 3), type text),

weekNumberColumn = Table.AddColumn(nameWeekdayShortUsColumn, "week_number", each Date.WeekOfYear([date_short], Day.Monday), Int64.Type),
weekNameColumn = Table.AddColumn(weekNumberColumn, "week_name", each "W" & Text.PadStart(Text.From([week_number]), 2, "0"), type text),

periodNumberColumn = Table.AddColumn(weekNameColumn, "period_number", each Date.Month([date_short]), Int64.Type),

periodNameColumn = Table.AddColumn(periodNumberColumn, "period_name", each if [period_number] < 10 then "P0" & Text.From([period_number]) else "P" & Text.From([period_number]), type text),

finalTable = Table.SelectColumns(periodNameColumn, {"date_short", "num_day", "num_month", "num_year", "name_month_br", "name_month_us", "name_month_short_br", "name_month_short_us","num_weekday", "name_weekday_short_br", "name_weekday_br", "name_weekday_short2_br", "name_weekday_us", "name_weekday_short_us", "week_number", "week_name", "period_number", "period_name"}),
#"Tipo Alterado" = Table.TransformColumnTypes(finalTable, {{"date_short", type date}})
in
#"Tipo Alterado"

[dufoq3 · Accepted solution]

Hi @jcnascime, I've updated few steps:

let
startDate = #date(2024, 1, 1),
endDate = #date(Date.Year(DateTime.LocalNow()), 12, 31),

numDays = Duration.From(endDate - startDate),
dateList = List.Dates(startDate, Number.From(numDays) + 1, #duration(1, 0, 0, 0)),
dateTable = Table.FromList(dateList, Splitter.SplitByNothing(), type table[date_short=date]),
numDayColumn = Table.AddColumn(dateTable, "num_day", each Date.Day([date_short]), Int64.Type),
numMonthColumn = Table.AddColumn(numDayColumn, "num_month", each Date.Month([date_short]), Int64.Type),
numYearColumn = Table.AddColumn(numMonthColumn, "num_year", each Date.Year([date_short]), Int64.Type),

nameMonthBrColumn = Table.AddColumn(numYearColumn, "name_month_br", each Date.ToText([date_short], "MMMM"), type text),
nameMonthUsColumn = Table.AddColumn(nameMonthBrColumn, "name_month_us", each Date.ToText([date_short], "MMMM", "en-US"), type text),
nameMonthShortBrColumn = Table.AddColumn(nameMonthUsColumn, "name_month_short_br", each Text.Start([name_month_br], 3), type text),
nameMonthShortUsColumn = Table.AddColumn(nameMonthShortBrColumn, "name_month_short_us", each Text.Start([name_month_us], 3), type text),

numWeekdayColumn = Table.AddColumn(nameMonthShortUsColumn, "num_weekday", each Date.DayOfWeek([date_short], Day.Monday), Int64.Type),
nameWeekdayShortBrColumn = Table.AddColumn(numWeekdayColumn, "name_weekday_short_br", each Text.Start(Date.ToText([date_short], "dddd"), 3), type text),
nameWeekdayBrColumn = Table.AddColumn(nameWeekdayShortBrColumn, "name_weekday_br", each Date.ToText([date_short], "dddd"), type text),
nameWeekdayShort2BrColumn = Table.AddColumn(nameWeekdayBrColumn, "name_weekday_short2_br", each Text.Start([name_weekday_br], 3), type text),
nameWeekdayUsColumn = Table.AddColumn(nameWeekdayShort2BrColumn, "name_weekday_us", each Date.ToText([date_short], "dddd", "en-US"), type text),
nameWeekdayShortUsColumn = Table.AddColumn(nameWeekdayUsColumn, "name_weekday_short_us", each Text.Start([name_weekday_us], 3), type text),

weekNumberColumn = Table.AddColumn(nameWeekdayShortUsColumn, "week_number", each Date.WeekOfYear([date_short], Day.Monday), Int64.Type),
weekNameColumn = Table.AddColumn(weekNumberColumn, "week_name", each "W" & Text.PadStart(Text.From([week_number]), 2, "0"), type text),
    periodNumberColumn = Table.AddColumn(weekNameColumn, "period_number", each 
        [ a = Date.StartOfMonth([date_short]), //Start of month
          b = Date.DayOfWeek(a, Day.Monday), //Day of week Start of month
          c = Date.AddDays(a, if b = 0 then 0 else 7 - b), //Fist Monday in current month
          d = Date.Month([date_short]), //month
          e = if [date_short] >= c then d else d - 1
        ][e], Int64.Type),


periodNameColumn = Table.AddColumn(periodNumberColumn, "period_name", each "P" & Text.PadStart(Text.From([period_number]), 2, "0"), type text),

finalTable = Table.SelectColumns(periodNameColumn, {"date_short", "num_day", "num_month", "num_year", "name_month_br", "name_month_us", "name_month_short_br", "name_month_short_us","num_weekday", "name_weekday_short_br", "name_weekday_br", "name_weekday_short2_br", "name_weekday_us", "name_weekday_short_us", "week_number", "week_name", "period_number", "period_name"})
in
    finalTable