List month end dates, between start and end date

[joemillson]

Hello,

I have a table for Scheduled Invoices, as follows:

Invoice ID	Start	End	Freq	Amount
1	31/05/2021	31/08/2021	Monthly	2000
2	31/05/2021	31/07/2021	Monthly	1000

And would like the above to become as below with Invoice Date listed, where Invoice Date = end of month date, of months between Start and End:

Invoice ID	Start	End	Freq	Amount	Invoice Date
1	31/05/2021	31/08/2021	Monthly	2000	31/05/2021
1	31/05/2021	31/08/2021	Monthly	2000	30/06/2021
1	31/05/2021	31/08/2021	Monthly	2000	31/07/2021
1	31/05/2021	31/08/2021	Monthly	2000	31/08/2021
2	31/05/2021	31/07/2021	Monthly	1000	31/05/2021
2	31/05/2021	31/07/2021	Monthly	1000	30/06/2021
2	31/05/2021	31/07/2021	Monthly	1000	31/07/2021

Thanks in advance

Joe

[AGo]

You can simply add custom column with:
Table.AddColumn(#"Previous step", "List", each {Number.From([Date start])..Number.From([Date end])})
Then change your column type to date, you'll have every single day so then you can summarize to month or year.

[Syndicate_Admin]

@Syndicate_Admin 

Please follow these steps in this article 

https://medium.com/dm-p/generating-rows-by-month-for-date-ranges-in-power-query-9baf62ed8e99

[ERD · Accepted solution]

Hi @joemillson ,

There is a solution on this forum: https://community.powerbi.com/t5/Power-Query/List-dates-between-two-dates/m-p/1005821 

So you

1. Create a custom function (New Source - Blank Query - Advanced Editor - Copy/Past the code).

2. In your table click Add Column - Invoke Custom Function.

3. Click Expand to New Rows.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.