How to expand rows based on start date and duration in months

[alanjwells]

HI all,

I'm a bit of a Power BI newbie and I'm trying to create a visual to display revenue by month based on project revenue, start date, duration in months for the next 12 month period.

Basically I have a table like this:

Project Name	Expected Revenue	Duration Months	Start_Month
A Project	100,000	5	10/12/2022
B Project	200,000	4	10/01/2023
C Project	500,000	5	27/02/2023
D Project	1,200,000	6	8/03/2023

Ideally I would be able to transferm the data into something like this:

Project Name	Expected Revenue	Duration Months	Start_Month	Month	Month Rev
A Project	100,000	5	10/12/2022	Dec-22	20000
A Project	100,000	5	10/12/2022	Jan-23	20000
A Project	100,000	5	10/12/2022	Feb-23	20000
A Project	100,000	5	10/12/2022	Mar-23	20000
A Project	100,000	5	10/12/2022	Apr-23	20000
B Project	200,000	4	10/01/2023	Jan-23	50000
B Project	200,000	4	10/01/2023	Feb-23	50000
B Project	200,000	4	10/01/2023	Mar-23	50000
B Project	200,000	4	10/01/2023	Apr-23	50000
C Project	500,000	5	27/02/2023	Feb-23	100000
C Project	500,000	5	27/02/2023	Mar-23	100000
C Project	500,000	5	27/02/2023	Apr-23	100000
C Project	500,000	5	27/02/2023	May-23	100000
C Project	500,000	5	27/02/2023	Jun-23	100000
D Project	1,200,000	6	8/03/2023	Mar-23	200000
D Project	1,200,000	6	8/03/2023	Apr-23	200000
D Project	1,200,000	6	8/03/2023	May-23	200000
D Project	1,200,000	6	8/03/2023	Jun-23	200000
D Project	1,200,000	6	8/03/2023	Jul-23	200000
D Project	1,200,000	6	8/03/2023	Aug-23	200000

Any assistance is appreciated.

Cheers,

A

[danextian · Accepted solution]

Hi @alanjwells ,

You can use Power Query to expand the rows with the month start as the starting point and the duration as the number of rows.  Please see attached pbix for your reference.

[alanjwells]

Awesome thanks, works perfectly.