generating list of months between two dates but the start and end date should include part months

[Joynul]

Start Date = 10/01/2022

End Date = 10/08/2022

I am looking to create a list where I can show I worked between these two dates, but the start date should still be 10/01/2022 and the end date should be 10/08/2022 all other months in between can be 01/02/2022, 01/03/2022, etc

Currently, I have got the code below but this only gives me the correct start date, but not the correct end date, please can you recommend what I can do to show the correct end date as well

let
StDt = [Start Date],
EndDt = [END Date],
AllDates = {Number.From([Start Date])..Number.From([END Date])},

StofMonthDates =
List.Distinct (
List.Select(
List.InsertRange(
List.Transform(
AllDates,
each Date.StartOfMonth(Date.From(_))
),
0,
{[Start Date]}
),
each Number.From(_) >= Number.From(StDt)
)
)
in
StofMonthDates

[AlexisOlson · Accepted solution]

How about this?

let
    StDt  = #date(2022,01,10),
    EndDt = #date(2022,08,10),
    Days = Duration.Days(EndDt - StDt),
    AllDates = List.Dates(StDt, Days + 1, #duration(1,0,0,0)),
    SelectDates = List.Select(AllDates, each _ = StDt or _ = EndDt or _ = Date.StartOfMonth(_))
in
    SelectDates

Result:

(In m/d/yyyy format)

[Joynul]

Both solutions work for me, thank you both for your support. I am going to accept the first one as that's the one I used initially 🙂

[AlexisOlson · Accepted solution]

How about this?

let
    StDt  = #date(2022,01,10),
    EndDt = #date(2022,08,10),
    Days = Duration.Days(EndDt - StDt),
    AllDates = List.Dates(StDt, Days + 1, #duration(1,0,0,0)),
    SelectDates = List.Select(AllDates, each _ = StDt or _ = EndDt or _ = Date.StartOfMonth(_))
in
    SelectDates

Result:

(In m/d/yyyy format)

[jbwtp · Accepted solution]

Hi all,

this one does not gerenate a full list of dates between start and end, hence does not need filtering:

let
    StDt  = #date(2022,01,10),
    EndDt = #date(2022,08,10),
    AllDates = List.Generate(()=>StDt, each _ < EndDt, each Date.StartOfMonth(Date.AddMonths(_, 1))) & {EndDt}
in
    AllDates

Cheers,

John