How to list all end-of-month dates between two dates on Power Query

[dcheng029]

Hi team,

I am working with a dataset where there is a list of items represented by the "ID" along with their respective "Received Date" and "Closed Date".

In order to graph the number of items outstanding within each month at the end of month, I am trying to created a column on Power Query which lists all of the end of month dates between the Received and Closed dates. Note if the item is not yet closed, it would show as outstanding for all EOM dates including the current month. Items received and closed in the same month would not return any outstanding month values.

What would be the best way to achieve this? Will be needing this to be done via PowerQuery so I can later separate the column by the comma delimiter.

ID	Received Date	Closed Date	Outstanding Month
1	5/08/2024	17/10/2024	Aug 24, Sep 24
2	12/08/2024	22/09/2024	Aug 24
3	16/09/2024	18/09/2024
4	22/10/2024	5/02/2025	Oct 24, Nov 24, Dec 24, Jan 25
5	17/11/2024	28/11/2024
6	20/12/2024	23/12/2024
7	27/12/2024	6/01/2025	Dec 24
8	5/01/2025	5/02/2025	Jan 25
9	17/02/2025	22/02/2025
10	29/03/2025	9/05/2025	Mar 25, Apr 25
11	4/04/2025	6/06/2025	Apr 25, May 25
12	12/04/2025		Apr 25, May 25, Jun 25
13	18/05/2025	1/06/2025	May 25
14	23/05/2025	28/05/2025
15	1/06/2025		Jun 25
16	1/06/2025	3/06/2025
17	3/06/2025		Jun 25
18	6/06/2025		Jun 25

[ronrsnfld]

And another method that seems to execute rapidly:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZDJEQUhCAVT+eV5qljcY7Em/zS+uKDMTbqFp7TmyD0uAhZg5NDPlIFwFu/THAviy3Mv6vFefFLUi2L8atkjRxbLOQ4dVyLp+KKF+CQIgVi910J8FpQv359CZ3yZgYt8w+sM32j+7fKEwiqg3xf6OV5edhcAw9Y9PF167079b3K/1hQ3J9u3/nk8F5tre3Ru+mBvx2aDtKuYhw/8/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Received Date" = _t, #"Closed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,{{"ID", Int64.Type}, {"Received Date", type date}, {"Closed Date", type date}},"en-GB"),
    
    #"Add Months List" = Table.AddColumn(#"Changed Type","Outstanding Month", (r)=>
            [a=Date.StartOfMonth(r[Received Date]),
             b=Date.StartOfMonth(r[Closed Date])??Date.AddMonths(Date.From(
                                                    Date.StartOfMonth(
                                                        DateTime.FixedLocalNow())),1),
             c=List.Generate(                       
                    ()=>[m=a],
                    each [m]<b,
                    each [m=Date.AddMonths([m],1)],
                    each [m]),
             
             d=List.Transform(c, each Date.ToText(_,"MMM yy")),
             e=Text.Combine(d,", ")
                    ][e], type text)
in
    #"Add Months List"

[MarkLaf]

Not literally what you are asking for but, given you eventually want to "separate the column by the comma delimiter," I'd recommend that you instead directly build the 'Outstanding Months' fact table from your original table. Adding the column transformation you are asking for and then splitting that column will not perform as well as just directly generating the fact table.

This fact table should be shaped such that your Items and Calendar dimension tables can be related to it. Here is some M to do this.

let
    Source = Original, // Replace 'Original' with your actual source table name
    // Function to convert a date to a month integer (YYYY * 12 + MM)
    DateToMonthInt = (x as date) as number => Date.Year(x) * 12 + Date.Month(x),
    // Function to convert a month integer back to the end of the month date
    MonthIntToEomDate = (x as number) as date =>
        [
            yrCalc = Int64.From(x / 12),
            moCalc = Number.Mod(x, 12),
            moCalc12 = if moCalc = 0 then 12 else moCalc,
            final = Date.EndOfMonth(#date(yrCalc, moCalc12, 1))
        ][final],
    // Combine received and closed dates into a list of outstanding months
    GetOpenDates = Table.CombineColumns(
        Source,
        {"Received Date", "Closed Date"},
        Value.ReplaceType(
            (cols as list) as nullable list =>
                [
                    received = cols{0},
                    // Default closed date to the end of the current month if null
                    closed = cols{1} ?? Date.EndOfMonth(Date.From(DateTime.LocalNow())),
                    isClosed = cols{1} <> null,
                    isClosedSameMonth = isClosed and Date.StartOfMonth(received) = Date.StartOfMonth(closed),
                    isNotClosedOnEOM = Date.EndOfMonth(closed) <> closed,
                    receivedMoInt = DateToMonthInt(received),
                    // Get the month integer, going one month back if closed date is not the end of the month
                    closedMoInt = DateToMonthInt(closed) - Int64.From(isNotClosedOnEOM),
                    openMoInts = {receivedMoInt..closedMoInt},
                    result = if isClosedSameMonth then null else List.Transform(openMoInts, MonthIntToEomDate)
                ][result],
            // Replace function type to get more accurate column type
            type function (cols as {nullable date}) as nullable {date}
        ),
        "Outstanding Months"
    ),
    ExpandOpenDates = Table.ExpandListColumn(GetOpenDates, "Outstanding Months")
in
    ExpandOpenDates

Result:

[ThxAlot]

Simple enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZDJEQUhCAVT+eV5qljcY7Em/zS+uKDMTbqFp7TmyD0uAhZg5NDPlIFwFu/THAviy3Mv6vFefFLUi2L8atkjRxbLOQ4dVyLp+KKF+CQIgVi910J8FpQv359CZ3yZgYt8w+sM32j+7fKEwiqg3xf6OV5edhcAw9Y9PF167079b3K/1hQ3J9u3/nk8F5tre3Ru+mBvx2aDtKuYhw/8/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Received Date" = _t, #"Closed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Received Date", type date}, {"Closed Date", type date}}, "fr"),

    #"Added Column" = Table.AddColumn(
        #"Changed Type",
        "Outstanding Month",
        each let
            rcv = [Received Date],
            closed = [Closed Date] ?? Date.From(Date.AddMonths(DateTime.LocalNow(),1)),
            span = (Date.Year(closed)-Date.Year(rcv))*12+(Date.Month(closed)-Date.Month(rcv))-1,
            eod = List.Transform({0..span}, each Date.ToText(Date.EndOfMonth(Date.AddMonths(rcv,_)), "MMM yyyy"))
        in Text.Combine(eod, ", ")
    )
in
    #"Added Column"

[PwerQueryKees]

And an alternative solution from me...

Starting with:

Using

let
    Source = Items,   // Replace Items with your own table name
    // Add a column with a list of all "outstanding" months
    #"Added List of Month Starts" = Table.AddColumn(Source, "List of Month Starts", each List.Transform(  // to convert each month to text
    List.Sort(  // sort dates in chronological order
        List.Generate( // find the months it is outstanding
            () => Date.StartOfMonth([Received Date]),  // starting with the start of the month received
            (M) => M < Date.StartOfMonth([Closed Date] ?? DateTime.Date(DateTime.FixedLocalNow())),  // ending BEFORE the start of with the month closed or the current month
            (M) => Date.AddMonths(M,1)  // skip to next month
        )
    ),
    (M) => Date.ToText(M, "MMM yy") // Making the actual transormation in the List.Transform()
)),
    // Add a colum converting the list of outstanding months to a text field.
    #"Added Outstanding Month" = Table.AddColumn(#"Added List of Month Starts", "Outstanding Month", each Text.Combine([List of Month Starts],","))
in
    #"Added Outstanding Month"

Resulting in

NOTE: I could have avoided or removed the column containing the list of months, but since you mentioned you intended to do a SplitByDelimiter(), I figured you could use this column instead...

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[SundarRaj]

Hi @dcheng029 , here's another solution to find the months in between the Receieved Date and the Closed Date. I'll leave the output and the M code below. Let me know if you'd need the file for better reference. Thanks!

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Received Date", type date}, {"Closed Date", type date}}),
Closed = Table.AddColumn ( #"Changed Type" , "Closed" , each if _[Closed Date] = null then [Received Date] else _[Closed Date] ),
List = Table.AddColumn ( Closed , "Between Months" , each List.Distinct ( List.Transform ( { Number.From( [Received Date] )..Number.From( [Closed] ) } , each Date.EndOfMonth ( Date.From ( _ ) ) ) ) ),
Dates = Table.AddColumn ( List , "Outstanding Months" , each if [Closed Date] = null then _[Between Months] else List.RemoveLastN ( _[Between Months] , 1 ) ),
ToText = Table.TransformColumns ( Dates , {"Outstanding Months" , each List.Transform ( _ , each Date.ToText ( _ , "MMM yy" ) ) } ),
#"Extracted Values" = Table.TransformColumns(ToText, {"Outstanding Months", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
Cols = Table.SelectColumns ( #"Extracted Values" , {"ID","Received Date","Closed Date","Outstanding Months"} )
in
Cols

[SamsonTruong]

Hi @dcheng029 ,

Please try the following Power Query (M) code to generate the "Outstanding Month" column. While implementing this, please replace 'YourOriginalTableNameHere' with your actual table name:

let
    Source = YourOriginalTableNameHere,
    NewStep = Table.AddColumn(Source, "Outstanding Month", (row) =>
        let
            StartDate = Date.StartOfMonth(row[Received Date]),
            EndDate = if row[Closed Date] = null then Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())) else Date.EndOfMonth(row[Closed Date]),
            AllMonths = List.Distinct(List.Transform(List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)), each Date.EndOfMonth(_))),
            Cleaned = if Date.Month(row[Received Date]) = Date.Month(row[Closed Date]) and Date.Year(row[Received Date]) = Date.Year(row[Closed Date]) then {} else AllMonths,
            MonthLabels = List.Transform(Cleaned, each Date.ToText(_, "MMM yy"))
        in
            Text.Combine(MonthLabels, ", ")
    )
in
    NewStep

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson