Hopfully someone can help with this. Not sure if it's possible or not.
I have a table that contains data for the date a control is initiated [initiated date] and the date it is due [control due]. Up until now the the due date is always 30 days from the initiation date. These are dates that are pulled from a system report and used in the PBI report. Within the table I have calcualted columns of assessment due [assessment due] and review due [review due]. These are done with a basic equation of [initiated date]+14 to get the [assessment due] date, and [initiated]+23 to get the [review due] date. Up to this point this has worked well because everything has always been due in 30 days. So if the initiation date is 1/1/23 I get an assessment due date of 1/15/23, a review due date of 1/24/23, and a due date of 1/30/23.
We are changing things and now the period between initiation and due date will change on a case by case basis. I need to figure out a way to calculate the dates so that the assessment due date will be 14 days before the [control due] and the [review due] is 7 days before the [control due]. The easy answer would be just do the calculation backwards from the [control due] date; however, in cases where the [control due] date is less than 30 days away from the [initiated date] it can cause the [assessment due] date to show as earlier than the [initiated date].
I'm thinking of creating a calculated column that detemines the number of days between the [initiated date] and [control due] and then somehow does a dynamic date based on that number, but not sure how to go about that, if that would be the best way to do it, or if that would even work.
So....if any of you that are way smartet than me at this stuff have any ideas on how to get this to work it would be greatly appreaciated. Thanks in advance for any help/guideance you can provide.
