Forum Discussion
Date Table with Varying Sales Month Periods
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
5 Replies
Hi Anonymous
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"Hi, Anonymous!
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?
- wdx223_Daniel
Community Champion