How to split a Date Range into months

[Anonymous]

Hi,

I have a data set with "Valid From" and "Valid To" dates which shows the price of a material. The date range is from 7/1/2016 to 10/31/2016. Is there any way to split the date range into 3 separate months? 

• 7/1/2016 to 8/1/2016
• 8/1/2016 to 9/1/2016
• 9/1/2016 to 10/1/2016

[v-alq-msft · Accepted solution]

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.

[Prudhvi_BSS]

Great solution @v-alq-msft .

But the only problem is whenever we give a start date other than the first day of month ,

the first row will have an end date in the next month

Is there a way we can correct it, so that even the first row is up to the end of first month?

[jhuici]

I need this too.

Please help us!