How to split a Date Range into months

Hi, Anonymous 

Based on your description, I created data to reproduce your scenario.

You may click ‘Edit Query’, go to Query Editor, choose ‘Add Column’.

New Valid From = let

IsCrossMonth = Date.Month([Valid to])<>Date.Month([Valid From]) or Date.Year([Valid to])<>Date.Year([Valid From]),

MonthDu =  (Date.Year([Valid to])-Date.Year([Valid From]))*12 +Date.Month([Valid to]) -Date.Month([Valid From])

in

List.Generate(()=> [ x = 0 , y = [Valid From]],each [x] <= MonthDu, each [y = [y],x =[x]+ 1],each if[x]=0 then [y] else Date.AddDays(Date.AddMonths([y],[x]),1-Date.Day([y])))

Secondly, you can click the ‘New Valid From’ column and choose ‘Expand to new rows’.

Thirdly, you can add a custom column as follows.

New Valid To = if[Valid From] = [New Valid From]

then if (Date.Year([Valid to])-Date.Year([Valid From]))*12 +Date.Month([Valid to]) -Date.Month([Valid From])<1

    then [Valid to]

    else Date.AddDays(Date.AddMonths([New Valid From],1),-1)

else if (Date.Year([Valid to])-Date.Year([New Valid From]))*12 +Date.Month([Valid to]) -Date.Month([New Valid From])<1

    then [Valid to]

    else

Date.AddDays(Date.AddMonths([New Valid From],1),-1)

Finally, you can remove ‘Valid From’ and ‘Valid To’ columns.

Result:

If I misunderstand your thought, please show me your sample data and expected output. I am glad to solve the problem for you. Here is my pbix .

Best Regards,

Allan

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