How to add days to an existing date via advanced formulae

[andrewbarlow1]

Hi - I need to add 84 days to an existing date in a model.

I am using 

MEMBERSET [d/Measures] = "Quantity"
MEMBERSET [d/Last_Modified_Date] = "2025-02-11"

DATA()=RESULTLOOKUP([d/GestationDate]=NEXT(84,"DAY",[d/Close_Date]))

However running this has no effect.

I only want to update any GestationDate where the Last_Modified_Date = '2025-02-11' (or preferably use the TODAY() function somehow ) with whatever is in the Close_Date + 84 days.

I have seen a couple of threads on this and from those I tried this but it has no effect when I run the data action.

Am I trying to perform the impossible?

The whole reason I am doing this is because I am using an API to push data into this model and there is no wrangling facility available or it would be a simple case of creating a calculated column of [GestationDate]=DATEADD([d/Close_Date],84,"day") and loading it in.

Also - I cannot add this calculated field to the source.

I was thinking if I can get this script to run on the hour a couple of minutes after the api runs it would do the trick.

Any help much appreciated.

[andrewbarlow1 · Accepted solution]

I had another think about what it was I was trying to achieve here and came up with a better solution.

In the model I opened up the Close_Date dimension and put it into user managed.

The dimension table was set to DAY MTH QTR YEAR so in Excel I just created a blank sheet and added dates of 2000-01-01 to 2030-12-31

For column 2 I just did an =A2+84 which added 84 days to col 1 and copied the formula to the end of the data (2030-12-31)

In my user managed date dimension I created a new property called Gestation of type date and pasted the value of my column into here, making sure my first value was lined up with the existing 2000-01-01.

Save the dimension and I now have an 84 day offset column I can use for the necessary joining of an other model so I can show the data in this model thrown forward by 84 days...