generate months between two columns

[munchkin666]

How to generate months between two columns. So for example if i Have Start of Month 1/1/2021 and Month Affected (TO) 2 1/5/2021. I wanted to generate a new column DateRange, whihc would create 5 rows for this ID with 5 diffrenet dates :1/1/2021, 1/2/2021, 1/3/2021, 1/4/2021, 1/5/2021.

I used this formula:

={Number.From([Start of Month])..Number.From([#"Month Affected (TO) 2"])}

but it generated many many many rows and isntead o a desired output, i received somehting like 1/1/2021, 2/1/2021, 3/1,2021....20/04/2021....etc. 

How add just the months not the days?

[PC2790 · Accepted solution]

Looks like, it haspicked up the datesinstead of the monthsin the formula due to the syntax of your column.

Try fetching the month only by using something like Date.Month,if your column's data type is Date.

[munchkin666]

@PC2790 @Anonymous  thank you both ideas are great. Learn something new every day. Thank you for your help

[Anonymous · Accepted solution]

Hi @munchkin666 

Please correct me if I wrongly understood your question.

I create a table like this:

And then use the date from table to create a new table 2 .

Table 2 = DISTINCT( SELECTCOLUMNS(CALENDAR(MAX('Table'[Column1]),MAX('Table'[Column2])),"Month",DATE(YEAR([Date]),MONTH([Date]),1)))

The effect is as shown :

Best Regards

Community Support Team _ Ailsa Tao

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

[PC2790 · Accepted solution]

Looks like, it haspicked up the datesinstead of the monthsin the formula due to the syntax of your column.

Try fetching the month only by using something like Date.Month,if your column's data type is Date.