8 day week, Monday to Monday

[DataTreker]

Create calculations for an 8 day (Monday to Monday) week

14m ago

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

[p45cal]

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
    ArrangedColumns

eg.:

[DataTreker]

lbendlin, thanks for the reply.  Could you give me an example of what that measure might look like?

[dufoq3]

Have you tested my query?

[dufoq3 · Accepted solution]

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

[DataTreker]

I 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]

Create a measure that calculates the sum of values for the given day and the seven days before it.