Forum Discussion
8 day week, Monday to Monday
My managers want a column of numbers added up each week. The problem is that they want a Monday to Monday week. I can do this for a single 8 day period with a date slicer in a visual but what I need to do is create a table where each row is a Monday to Monday (8 days) week and the corresponding measure that sums up data for that week. I am aware of the 1 day overlap of data.
The data comes from an imported excel table.
| Date | Qty |
| 1-Jan-24 | 10 |
| 2-Jan-24 | 30 |
| 3-Jan-24 | 28 |
| . | |
| . | |
| . | |
| 22-Jan | 19 |
I just cannot figure out how to make a custom 8-day week date range for a table that would look something like this:
| Week of Year | Week Start Date | Week End Date | Week total |
| 1 | 1-Jan-24 | 8-Jan-24 | 75 |
| 2 | 8-Jan-24 | 15-Jan-24 | 90 |
| 3 | 15-Jan-24 | 22-Jan-24 | 50 |
Hi DataTreker, check this:
Comment: QTY are generated randomly (if you click refresh preview or you choose different steps - numbers will change).
Output
let Source = Table.FromRows(List.Zip({ List.Dates(#date(2024,1,1), 366, #duration(1,0,0,0)), List.Transform({1..366}, (x)=> Number.Round(Number.RandomBetween(x, 1000), 0)) }), type table[Date=date, QTY=number]), AD_DayOfWeek = Table.AddColumn(Source, "Day of Week", each Date.DayOfWeek([Date], Day.Monday), Int64.Type), Buffered = Table.Buffer(AD_DayOfWeek), Helper = [ mondayPositions = List.PositionOf(Buffered[Day of Week], 0, Occurrence.All), positions = List.Positions(mondayPositions), zipped = List.Zip({ mondayPositions, positions }) ], StepBack = Buffered, DupcilateMondays = List.Accumulate( Helper[zipped], StepBack, (s,c)=> Table.InsertRows(s, c{0} + c{1}, {Buffered{c{0}}} ) ), DayOfWeekBuffered = List.Buffer(DupcilateMondays[Day of Week]), GeneratedGroupHelper = List.Generate( ()=> [ x = 0, y = DayOfWeekBuffered{x}, z = x ], each [x] < List.Count(DayOfWeekBuffered), each [ x = [x]+1, y = DayOfWeekBuffered{x}, z = if y = [y] then [z]+1 else [z] ], each [z] ), Combined = Table.FromColumns(Table.ToColumns(DupcilateMondays) & {GeneratedGroupHelper}, Value.Type(Table.FirstN(DupcilateMondays, 0) & #table(type table[GroupHelper=Int64.Type], {}))), GroupedRows = Table.Group(Combined, {"GroupHelper"}, {{"Start Date", each List.Min([Date]), type date}, {"End Date", each List.Max([Date]), type date}, {"QTY", each List.Sum([QTY]), type number}}), RemovedColumns = Table.RemoveColumns(GroupedRows,{"GroupHelper"}), FilteredRows = Table.SelectRows(RemovedColumns, each [Start Date] <> [End Date]), Ad_Week = Table.AddColumn(FilteredRows, "Week", each Date.WeekOfYear([Start Date]), Int64.Type) in Ad_Week
7 Replies
- dufoq3
Community Champion
Hi DataTreker, check this:
Comment: QTY are generated randomly (if you click refresh preview or you choose different steps - numbers will change).
Output
let Source = Table.FromRows(List.Zip({ List.Dates(#date(2024,1,1), 366, #duration(1,0,0,0)), List.Transform({1..366}, (x)=> Number.Round(Number.RandomBetween(x, 1000), 0)) }), type table[Date=date, QTY=number]), AD_DayOfWeek = Table.AddColumn(Source, "Day of Week", each Date.DayOfWeek([Date], Day.Monday), Int64.Type), Buffered = Table.Buffer(AD_DayOfWeek), Helper = [ mondayPositions = List.PositionOf(Buffered[Day of Week], 0, Occurrence.All), positions = List.Positions(mondayPositions), zipped = List.Zip({ mondayPositions, positions }) ], StepBack = Buffered, DupcilateMondays = List.Accumulate( Helper[zipped], StepBack, (s,c)=> Table.InsertRows(s, c{0} + c{1}, {Buffered{c{0}}} ) ), DayOfWeekBuffered = List.Buffer(DupcilateMondays[Day of Week]), GeneratedGroupHelper = List.Generate( ()=> [ x = 0, y = DayOfWeekBuffered{x}, z = x ], each [x] < List.Count(DayOfWeekBuffered), each [ x = [x]+1, y = DayOfWeekBuffered{x}, z = if y = [y] then [z]+1 else [z] ], each [z] ), Combined = Table.FromColumns(Table.ToColumns(DupcilateMondays) & {GeneratedGroupHelper}, Value.Type(Table.FirstN(DupcilateMondays, 0) & #table(type table[GroupHelper=Int64.Type], {}))), GroupedRows = Table.Group(Combined, {"GroupHelper"}, {{"Start Date", each List.Min([Date]), type date}, {"End Date", each List.Max([Date]), type date}, {"QTY", each List.Sum([QTY]), type number}}), RemovedColumns = Table.RemoveColumns(GroupedRows,{"GroupHelper"}), FilteredRows = Table.SelectRows(RemovedColumns, each [Start Date] <> [End Date]), Ad_Week = Table.AddColumn(FilteredRows, "Week", each Date.WeekOfYear([Start Date]), Int64.Type) in Ad_WeekI just copied your code into a blank query. I'm going to spend a bit studying it to see if I can understand what you did to make it work.
- dufoq3
Read note below my post if you don't know how to use it.
- lbendlin
Super User
Create a measure that calculates the sum of values for the given day and the seven days before it.
lbendlin, thanks for the reply. Could you give me an example of what that measure might look like?
- dufoq3
Have you tested my query?
- p45cal
Solution Supplier
Borrowing dufoq3 's source:
let Source = Table.FromRows(List.Zip({ List.Dates(#date(2024,1,1), 366, #duration(1,0,0,0)), List.Transform({1..366}, (x)=> Number.Round(Number.RandomBetween(x, 1000), 0)) }), type table[Date=date, Qty=number]), InsertedWeekTotals = Table.AddColumn(Source, "Week Total", each if Date.DayOfWeek([Date])=0 then let Dte1 = [Date],result=List.Sum(Table.SelectRows(Source, each [Date] >= Dte1 and [Date] <= Date.AddDays(Dte1,7))[Qty]) in result else null,type number), FilteredRows = Table.SelectRows(InsertedWeekTotals, each ([Week Total] <> null)), AddWeekEndDate = Table.AddColumn(FilteredRows, "Date Week End", each Date.AddDays([Date],7),type date), AddWeekOfYear = Table.AddColumn(AddWeekEndDate, "Week of Year", each Date.WeekOfYear([Date])), ArrangedColumns = Table.SelectColumns(AddWeekOfYear,{"Week of Year", "Date", "Date Week End", "Week Total"}) in ArrangedColumnseg.:
