Forum Discussion
jcnascime
2 years agoRegular Visitor
Calendar Power BI
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 years ago
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
dufoq3
2 years agoCommunity Champion
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