Creating a date table with Broadcast Month and Year - Help with formulas

[hoyt22]

Hi all,

I work in media, and we typically use broadcast weeks instead of calendar weeks. I'm creating a date table and managed to figure out the broadcast week formula, but broadcast month is proving difficult.

Broadcast Week: Begins on Monday and ends on Sunday

Broadcast Month: The week that contains the first of the month becomes the first week of the broadcast month. For example, October 1, 2024 was a Tuesday, so the broadcast week of September 30th (the start of the broadcast week) falls in October.

Here's my current code with broadcast week. I'd appreciate any help with figuring out broadcast month.

let
    StartDate = #date(2024,1,1),
    EndDate = #date(2024,12,31),
    NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Added Custom Columns" = Table.AddColumn(#"Converted to Table", "Calendar Year", each Date.Year([Date])),
    #"Added Custom Columns1" = Table.AddColumn(#"Added Custom Columns", "Calendar Quarter", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
    #"Added Custom Columns2" = Table.AddColumn(#"Added Custom Columns1", "Calendar Month Number", each Date.Month([Date])),
    #"Added Custom Columns3" = Table.AddColumn(#"Added Custom Columns2", "Calendar Month Name", each Date.ToText([Date], "MMMM")),
    #"Added Custom Columns4" = Table.AddColumn(#"Added Custom Columns3", "Day", each Date.Day([Date])),
    #"Added Custom Columns5" = Table.AddColumn(#"Added Custom Columns4", "Broadcast Week", each Date.StartOfWeek([Date], Day.Monday)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Columns5",{{"Date", type date}})
in
    #"Changed Type"

Thank you!

[v-stephen-msft]

Hi @hoyt22 ,

audreygerred's insights were great.

Here's my solution for your reference. I continued on your code and got the following number of weeks.

Below is code you can refer to.

let
    StartDate = #date(2024,1,1),
    EndDate = #date(2024,12,31),
    NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Added Custom Columns" = Table.AddColumn(#"Converted to Table", "Calendar Year", each Date.Year([Date])),
    #"Added Custom Columns1" = Table.AddColumn(#"Added Custom Columns", "Calendar Quarter", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
    #"Added Custom Columns2" = Table.AddColumn(#"Added Custom Columns1", "Calendar Month Number", each Date.Month([Date])),
    #"Added Custom Columns3" = Table.AddColumn(#"Added Custom Columns2", "Calendar Month Name", each Date.ToText([Date], "MMMM")),
    #"Added Custom Columns4" = Table.AddColumn(#"Added Custom Columns3", "Day", each Date.Day([Date])),
    #"Added Custom Columns5" = Table.AddColumn(#"Added Custom Columns4", "Broadcast Week", each Date.StartOfWeek([Date], Day.Monday)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Columns5",{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Broadcast Week"}, {{"allrows", each _, type table [Date=nullable date, Calendar Year=number, Calendar Quarter=text, Calendar Month Number=number, Calendar Month Name=text, Day=number, Broadcast Week=date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Week", each "Week"& Text.From([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Expanded allrows" = Table.ExpandTableColumn(#"Removed Columns", "allrows", {"Date", "Calendar Year", "Calendar Quarter", "Calendar Month Number", "Calendar Month Name", "Day"}, {"Date", "Calendar Year", "Calendar Quarter", "Calendar Month Number", "Calendar Month Name", "Day"})
in
    #"Expanded allrows"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hoyt22]

Thanks! I don't see Broadcast Month in your code, but I think I was able to figure it out. It seems to work even though I don't quite understand why yet.

I included the code below in case it helps anyone in the future:

let
    StartDate = #date(2021, 12, 27),
    EndDate = #date(2024, 12, 31),
    NumberOfDays = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Added Custom Columns" = Table.AddColumn(#"Converted to Table", "Calendar Year", each Date.Year([Date])),
    #"Added Custom Columns1" = Table.AddColumn(#"Added Custom Columns", "Calendar Quarter", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
    #"Added Custom Columns2" = Table.AddColumn(#"Added Custom Columns1", "Calendar Month Number", each Date.Month([Date])),
    #"Added Custom Columns3" = Table.AddColumn(#"Added Custom Columns2", "Calendar Month", each Date.ToText([Date], "MMMM")),
    #"Added Custom Columns4" = Table.AddColumn(#"Added Custom Columns3", "Calendar Day", each Date.Day([Date])),
    #"Added Custom Columns5" = Table.AddColumn(#"Added Custom Columns4", "Broadcast Day", each Date.Day([Date])),
    #"Added Custom Columns6" = Table.AddColumn(#"Added Custom Columns5", "Broadcast Week", each Date.StartOfWeek([Date], Day.Monday)),
    #"Added Custom Columns7" = Table.AddColumn(#"Added Custom Columns6", "Broadcast Month", 
        each 
            let
                // Start of the month for the current date
                FirstOfNextMonth = Date.StartOfMonth(Date.AddMonths([Date], 1)),
                // Monday of the week containing the 1st of the next month
                FirstWeekOfNextMonth = Date.StartOfWeek(FirstOfNextMonth, Day.Monday)
            in
                if [Date] >= FirstWeekOfNextMonth 
                then Date.ToText(FirstOfNextMonth, "MMMM") 
                else Date.ToText(Date.StartOfMonth([Date]), "MMMM")
    ),
    
    #"Added Custom Columns8" = Table.AddColumn(#"Added Custom Columns7", "Broadcast Month Number", 
        each List.PositionOf({"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, [Broadcast Month]) + 1
    ),
    #"Added Custom Columns9" = Table.AddColumn(#"Added Custom Columns8", "Broadcast Quarter", 
        each 
            if [Broadcast Month] = "January" or [Broadcast Month] = "February" or [Broadcast Month] = "March" then "Qtr 1"
            else if [Broadcast Month] = "April" or [Broadcast Month] = "May" or [Broadcast Month] = "June" then "Qtr 2"
            else if [Broadcast Month] = "July" or [Broadcast Month] = "August" or [Broadcast Month] = "September" then "Qtr 3"
            else if [Broadcast Month] = "October" or [Broadcast Month] = "November" or [Broadcast Month] = "December" then "Qtr 4"
            else null
    ),
    #"Added Custom Columns10" = Table.AddColumn(#"Added Custom Columns9", "Broadcast Year", 
    each if [Date] >= Date.StartOfWeek(Date.AddDays(Date.StartOfMonth(Date.AddMonths([Date], 1)), -Date.DayOfWeek(Date.StartOfMonth(Date.AddMonths([Date], 1)), Day.Monday)), Day.Monday) 
    then Date.Year(Date.AddMonths([Date], 1)) 
    else Date.Year([Date])
),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Columns10",{{"Date", type date}, {"Broadcast Month Number", Int64.Type}, {"Calendar Month Number", Int64.Type}, {"Broadcast Week", type date}})
in
    #"Changed Type"

[audreygerred]

Hi! SQLBI has a date reference template table that should work well here. You can download it from their site and add it into your model as a date table. It works as a regular calendar, fiscal calendar, or fiscal weekly. You can choose what month is the first in the year, what day of the week a week starts, etc. I would imagine you should be able to get the fiscal weekly to work with your needs well. I use this all the time, and it is especially helpful when I am doing models that need to follow the NRF calendar. DAX Date Template - SQLBI

Here is a link that explains the table more. The above link is where you can download it from: Reference Date Table in DAX and Power BI - SQLBI

[hoyt22]

Thanks! I'll take a look.

At first glance, it seems more complicated than what I need. Ideally, there's just a formula I need to add to my current date table.