Create a Table with StartDate and EndDate of Week for a Determined Period

[Anonymous]

Hi, I´m trying to create a table that contains the start date of a week and the end date of a week given a "number of periods days" and a "number of periods to be consider". For example.

Origen = 01/07/2021 (today)

numberOfPeriodsDays=7 (is equivalent a 1 week)

numberOfPeriodsToBeConsider=5 (is equivalente a 5 weeks)

This is the result I want to create with format (dd/mm/yyyy)

StartDate	EndDate
24/05/2021	30/05/2021
31/05/2021	06/06/2021
07/06/2021	13/06/2021
14/06/2021	20/06/2021
21/06/2021	27/06/2021
28/06/2021	04/07/2021

I trying to use List.Generate but I can't make it work, perhaps I need another approach

    Origen = DateTimeZone.LocalNow(),
    StartDateOfPeriod= Date.StartOfWeek(Date.AddDays(DateTimeZone.LocalNow(),(-1)*numberOfPeriodsDays*numberOfPeriodsToBeConsider)),
    GeneratePeriods=List.Generate(
        ()=> [
            Counter=0, 
            PeriodsTable=#table
            (type table 
            [
                "StartDate"=date,
                "EndDate"=date
            ],
            {StartDateOfPeriod,Date.EndOfWeek(StartDateOfPeriod)}
            )
            ],
            each Table.Last([PeriodsTable])[EndDate]>Origen,
            each [
                Counter=[Counter]+1,  
                NewStartDateOfPeriod = Date.AddDays([StartDate],numberOfPeriodsDays), 
                NewEndDateOfPeriod=Date.EndOfWeek(NewStartDateOfPeriod), 
                PeriodsTable=Table.Combine({[PeriodsTable],Table.FromRecords({NewStartDateOfPeriod,NewEndDateOfPeriod})})
                ],
            each [PeriodsTable]),
    PeriodTable= List.Last(GeneratePeriods) 

 I really appreciate any help!

Thanks,

Julio

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
Origin = Date.From(DateTime.LocalNow()),
DaysPerPeriod = 7,
NumberOfPeriods = 5,
    InitialList = {-1*NumberOfPeriods+1 .. 0},
    Custom1 = List.Transform(InitialList, each [StartDate=Date.StartOfWeek(Date.AddDays(Origin,_ * DaysPerPeriod)), EndDate = Date.EndOfWeek(Date.AddDays(Origin,_ * DaysPerPeriod)) ]),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"StartDate", "EndDate"}, {"StartDate", "EndDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"StartDate", type date}, {"EndDate", type date}})
in 
#"Changed Type"

You can tweak the Date.StartOfWeek and EndOfWeek for which day of the week you want to start/end the weeks on with Day.Saturday, etc. if needed.  You can also choose the desired date format after you load the table.

Pat

[Anonymous]

Thank you so much!!