Forum Discussion

jcnascime's avatar
jcnascime
Regular Visitor
2 years ago
Solved

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...
  • dufoq3's avatar
    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