Date Table with Varying Sales Month Periods

[dbethart]

Hello!

I am VERY new to Power BI. I am working on building a simple Date Table and I need help with adding a column for our Sales Month.

Currently, our Sales Month begins on the Sunday after the 1st Saturday of the Month and ends on the 1st Saturday of the next month - so... Sales Month for June begins on Sunday, June 6, 2021 and ends on Saturday, July 3, 2021.

Using Power Query, how can I add this Sales Month column to my Date Table and have it populate correctly?

Thanks in advance for your help!

DB

[wdx223_Daniel · Accepted solution]

[dbethart]

This worked perfectly, thank you!

[Vera_33]

Hi @dbethart 

Here is one way, paste it to Advaned Editor with a blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdGxDYNAAATBXogt8XfYvF0Lov82MAHBhJuNtMexjKxjXzua5Xz9s+Zmvs2PuZvT/Jo/MsNUFVVRFVVRFVVRFVVRVVVVVVVVVVVVVVVVVVVVtam6D04PTg9ODz55Xg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([Date],0), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Day of Week", "Month", each Date.Month([Date]), Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Month", each ([Day of Week] = 0)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Month"}, {{"Start", each List.Min([Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Month", {"Month"}, #"Grouped Rows", {"Month"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Sales Month", each if [Date]<[Grouped Rows][Start]{0} then [Month]-1 else [Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped Rows"})
in
    #"Removed Columns"

[dbethart]

Hi, @Vera_33!

Thank you for putting this together for me. I'm having a few issues:

• The Date in this query is formatted as dd/mm/yyyy - I need it to be mm/dd/yyyy. This is causing the query to error out on my end. How do I change that?
• It looks like the query only contains 34 rows (or about 1 month's worth of dates). How do I expand that to include every day in 2020-2025?

   

[Vera_33]

Hi @dbethart 

The query is for your refernce, you go with your own data, start from #"Inserted Month"(it's in the UI, you can click)  with Month...you don't need to use the sample data I put in the query, use your own