Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
dcheng029
Helper II
Helper II

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

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.

 

IDReceived DateClosed DateOutstanding Month
15/08/202417/10/2024Aug 24, Sep 24
212/08/202422/09/2024Aug 24
316/09/202418/09/2024 
422/10/20245/02/2025Oct 24, Nov 24, Dec 24, Jan 25
517/11/202428/11/2024 
620/12/202423/12/2024 
727/12/20246/01/2025Dec 24
85/01/20255/02/2025Jan 25
917/02/202522/02/2025 
1029/03/20259/05/2025Mar 25, Apr 25
114/04/20256/06/2025Apr 25, May 25
1212/04/2025 Apr 25, May 25, Jun 25
1318/05/20251/06/2025May 25
1423/05/202528/05/2025 
151/06/2025 Jun 25
161/06/20253/06/2025 
173/06/2025 Jun 25
186/06/2025 Jun 25
9 REPLIES 9
v-echaithra
Community Support
Community Support

Hi @dcheng029 ,

Thanks @sanalytics , @Nasif_Azam , @ronrsnfld , @MarkLaf 

 

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

 

Regards,
Chaithra E.

sanalytics
Super User
Super User

@dcheng029 
Here is my my solution in Power query as well

let
    Source = Table,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Received Date", type date},{"Closed Date", type date}}),
    Result = 
Table.Group(
let 
varListOfDate = 
Table.AddColumn(#"Changed Type", "ListofDate", each List.Dates(
  Date.StartOfMonth([Received Date]),
Number.From(
if [Closed Date] = null then 
Date.EndOfMonth(
    DateTime.LocalNow()
)
else 
Date.StartOfMonth([Closed Date])
)
 - 
Number.From(
Date.StartOfMonth([Received Date])),#duration(1,0,0,0)
)),
varExpansion = 
Table.ExpandListColumn(varListOfDate, "ListofDate"),
varMonthFormat = 
Table.AddColumn(varExpansion, "Custom", each Date.ToText([ListofDate],"MMM-yyyy"))
in
varMonthFormat, {"ID","Received Date","Closed Date"}, {{"OutstandingMonth", each 
Text.Combine(
List.Distinct(
[Custom] ),",")}})
in
Result

 

Below screenshot

sanalytics_0-1749379501378.png

 

Regards

sanalytics

Nasif_Azam
Continued Contributor
Continued Contributor

Hey @dcheng029 ,

To generate a column in Power Query listing all end-of-month (EOM) dates between the Received Date and Closed Date (or current month if still open), follow the steps below:

Objective

You want:

  • A list of Month Year (e.g., Aug 24) values between Received Date and Closed Date, only if:

    • The item spans across multiple months.

    • It's still open → include all months till current month.

    • Same month → no value.

Power Query Solution

Step 1: Add a new "Today" column (if not closed)

= Table.AddColumn(Source, "Adjusted Closed Date", each if [Closed Date] = null then Date.EndOfMonth(DateTime.LocalNow()) else [Closed Date])

Step 2: Add a column to generate a list of EOM dates between Received Date and Adjusted Closed Date

= Table.AddColumn(#"Previous Step", "EOM Dates", each 
    let
        start = Date.EndOfMonth([Received Date]),
        end = Date.EndOfMonth([Adjusted Closed Date]),
        list = List.Distinct(List.Generate(
            ()=> start,
            each _ <= end,
            each Date.EndOfMonth(Date.AddMonths(_, 1))
        ))
    in
        list
)

Step 3: Format as MMM yy and remove unnecessary months

= Table.TransformColumns(#"Previous Step", {"EOM Dates", each 
    let
        formatted = List.Transform(_, each Date.ToText(_, "MMM yy")),
        removeIfSameMonth = if Date.Month([Received Date]) = Date.Month([Adjusted Closed Date]) 
                            and Date.Year([Received Date]) = Date.Year([Adjusted Closed Date])
                            then {}
                            else formatted
    in
        removeIfSameMonth
})

Step 4: Combine into a text string

= Table.AddColumn(#"Previous Step", "Outstanding Month", each Text.Combine([EOM Dates], ", "))

Output Sample

ID Received Date Closed Date Outstanding Month

105/08/202417/10/2024Aug 24, Sep 24
316/09/202418/09/2024(blank)
1212/04/2025(null)Apr 25, May 25, Jun 25

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam

ronrsnfld
Super User
Super User

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"

 

ronrsnfld_1-1749345616914.png

 

 

 

MarkLaf
Solution Sage
Solution Sage

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:

 

MarkLaf_0-1749330615102.png

 

ThxAlot
Super User
Super User

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"

ThxAlot_0-1749322187940.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



PwerQueryKees
Super User
Super User

And an alternative solution from me...

 

Starting with:

PwerQueryKees_0-1749287885896.png

 

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

PwerQueryKees_1-1749287997593.png

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
Solution Supplier
Solution Supplier

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!

SundarRaj_0-1749279220026.png

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

Sundar Rajagopalan
SamsonTruong
Solution Supplier
Solution Supplier

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.