Sorting a Date Table with Broadcast Dates

[hoyt22]

Hi all,

I work in media, so I have a date table with calendar time periods and broadcast time periods. I believe everything is set up correctly to figure out broadcast year, quarter, month, and week. However, I'm having trouble getting broadcast day to sort correctly in my chart.

For example, below I'm showing the Broadcast Month of February 2024, which includes four Broadcast Weeks: January 29, February 5, February 12, and February 19. When I add in Broadcast Day, it's not sorting them in the way I expect.

In the week of January 29, it should sort January 29, January 30, January 31, February 1, February 2, etc.

When I try to sort Broadcast Day by another column in my Date Table, I get an error.

I'd appreciate it if anyone could help. I have my example Power BI file and csv here.

Thanks!

[Sergii24]

Hi @hoyt22, I'm afraid it's impossible to achieve the desired result in the way you formulated the task. You're on a right track when thinking of using "sort by another column feature". This is indeed the preffered way to resolve similar cases, however in your case the same value might have more than 1 value, which is not allowed.

Let's take 29th as an example: depending on year-month, it might be within a week that is within the same month or different. Therefore, the same value of "Broadcast Day" might have 2 different values for any type of "sorting by" column.

To obtain the desired result, you might consider using other columns:

• date - this is unique and you have the order, therefore it will be sorted correctly
• day of week - also day of week will work as its values from 1 to 7 (or 0 to 6 depending on your time zone) will always respect the rule of 1 sorting value per 1 column value.

I hope it will help you! Good luck with your project 🙂

[hoyt22]

@Sergii24 Thank you for your assistance! I'd love to pick your brain further if you can help.

By Date:

I do see that using Date will work, but the date is just too long to make the chart look nice. Is there a way to create a column with a shortened version of the date that won't cause the same problem with sorting? I know I can change the format in the Column Tools menu, but there's no option to just show the day. Even showing "mmmm d" looks a bit long, though it's doable if it's the only option.

Day of Week:

I tried creating a Day of Week column, and when I tried to sort Broadcast Day by Day of Week (1-7), I ran into the same "more than one value" error. I may have misunderstood your comment. Were you using Day of Week to sort or just use in the chart?

I appreciate your help!

[v-xinc-msft]

Hi @hoyt22 ,

Please check out the steps below:

1. Create the file with date and figure it out:

2. Please check out the effect:

3. Could you please post your sample file here if this couldn’t help you resolve the issue? I can’t open the link you provided. How to provide sample data in the Power BI Forum - Microsoft Fabric Community”

Best regards,

Lucy Chen

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

[hoyt22]

@v-xinc-msft I'm not sure why you can't open my Google Drive link; it looks like another user was able to just fine.

I can't see your full code to see if it would solve my issue, but looking at your chart, you have both January and February selected. However, the Broadcast Week of January 29 should be under the Broadcast Month of February only.

Here's my date table in case it helps. Maybe there's something I'm missing.

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", 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"

I appreciate your help!

[Ashish_Mathur]

Hi,

In the Date Table, change the data type of the Broadcast day column as Whole number.

[hoyt22]

@Ashish_Mathur Thank you, but unfortunately, that doesn't solve my problem. Your solution sorts the broadcast days numerically, but that's not exactly what I need.

Here's what your solution did:

But the 29 is January 29, so it should be listed first. The correct order should be 29, 30, 31, 1, 2, 3, 4. That's because the Broadcast Week of January 29 contains the dates: January 29-31 and February 1-4.

[Ashish_Mathur]

Sorry, i misunderstood.