Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding dates for auto renewals in future

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!
 
 
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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])

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.