Forum Discussion
List month end dates, between start and end date
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
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.
3 Replies
- ERD
Community Champion
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.
Syndicate_AdminAdministrator
Please follow these steps in this article
https://medium.com/dm-p/generating-rows-by-month-for-date-ranges-in-power-query-9baf62ed8e99
AGoPost Patron
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.