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 ...
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?
Hi Anonymous
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