Add.Months multipletimes

[ThomasWeppler]

Hi Power Bi community

I have a table with 5 columns. The table shows how often an assingment should be reopened. 

1. Index: An index column
2. Interval [Number]: How many month should go between the reopening the assignment.

3. Assinment [Number]: This colum contains all assingments (For this question we can just focus on assingment number 1.
4. FirstDate [Date]: The first date the assingment was opened (So this is gonna be the same date in all rows for assingment 1)

5. Date [Date]: This column contain each date from the [FirstDate] and three years into the future from today. for each assingment.

I want to add the interval to the FirstDate to see when the next time the assingment is opened 


= Table.AddColumn(#"Add something", "new column", each if [Assinment] = 1 then
Date.AddMonths([FirstDate], [Interval]) else 0)

My problem is that I want the [new column] to always be higher than the [Date] and if this is not the case than it should add a number of month equal [Interval], but I don't know how to do that.

Can anyone here help? All help is greatly appreciated.

[JamesRobson · Accepted solution]

Not sure I fully understand your desired outcome so might be worth adding an example of what you expect?

However that said I this might be it:

= Table.AddColumn(#"Added Date", "new column", each if [Assinment] = 1 then if Date.AddMonths([FirstDate], [Interval]) < [Date] then Date.AddMonths([FirstDate],Number.RoundUp((Duration.Days([Date]-[FirstDate])/30)/[Interval])*[Interval]) else
Date.AddMonths([FirstDate], [Interval]) else 0, type date)

Thanks,

[ThomasWeppler]

It works as a charm. Thanks for the great solution and quick response.