Adding dates for auto renewals in future

[Anonymous]

Hi team,

I have a quite specific question about adding a date in the future to a BI Desktop report.

I have pulled two columns into my report called "start date" and "end date" for a contract. As the "end date" column was empty when I pulled them in, I added a new column called "new end date" that would automatically add two years to the "start date". It looks like this:

New End Date =

IF(ISBLANK('Contract'[End Date]),

DATE(YEAR('Contract'[Start Date]) + 2, MONTH('Contract'[Start Date]), DAY('Contract'[Start Date])),

'Contract'[End Date])

Now some of the dates in the "new end date" column are in the past, for example when the start date was in 2014 (the new end date would be somewhen in 2016 in this case). 

I would now like to create another column that will recognise that the "new end date" is in the past (for example through the < TODAY function) and then automatically add 2 years but until we have reached a date in the future (i.e. > TODAY). Taking the example from above, the function would recognise that 2016 is in the past and then automatically add 2 years until it reaches a date in this future (in this case, it would be 2022, since 2018 and 2020 are in the past too).

The reason why I am building this is because we are talking about auto renewals of a contract which is every two years unless the client cancels.

Thanks for you help here!

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

To create a calculate column like below:

Another Column =
VAR _ThisYear =
    YEAR ( TODAY () )
VAR _DateYear =
    YEAR ( 'Table'[New End Date] )
VAR _diff2 =
    QUOTIENT ( _ThisYear - _DateYear, 2 )
VAR _autoDate =
    EDATE ( 'Table'[New End Date], _diff2 * 24 )
VAR _autoDate2 =
    EDATE ( 'Table'[New End Date], ( _diff2 - 1 ) * 24 )
RETURN
    IF ( _autoDate > TODAY (), _autoDate2, _autoDate )

Result:

Please refer to the attachment below for details

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try like

New End Date =
var _diff = quotient(datediff('Contract'[Start Date], today(),year),2)+2
return
IF(ISBLANK('Contract'[End Date]),
DATE(YEAR('Contract'[Start Date]) + _diff, MONTH('Contract'[Start Date]), DAY('Contract'[Start Date])),
'Contract'[End Date])