Retrieve a set of members as the difference between two dates in properties

[fulmil]

Hello

I am trying to set up an advanced formulas script inside a data action to copy data from member "000000" of the Date dimension to a dynamic set of members of the Date dimension, which must correspond to the months between two dates (start date and end date). The start date and end date are both properties of another dimension (PRICELIST), and will vary by member. As for the rest of the dimensions, the values will be the same as the ones copied:

How can I retrieve the list of members of the Date dimension on which to write the data copied from "000000"?

I tried the following code:

DATA ([d/Measures] = "LIST",

[d/Date] = PERIOD ([d/PRICELIST].[p/VALID_FROM])

TO

PERIOD ([d/PRICELIST].[p/VALID_TO])) =

RESULTLOOKUP ([d/Measures] = "LIST",[d/Date]="000000")

But it gives me the error "Mismatching input TO". It does not seem to acknowledge the "TO" keyword.

Any ideas?

Thanks in advance

[N1kh1l]

Hello

When asking queries on Data Action its always easier if you put up an example to demonstrate your requirements. It will help the community understand your query better and provide appropriate response

For your requirement i think you would need a FOR EACH loop on pricelist dimension and check if date is between the start date and end date ( you could use datediff for that )

Nikhil

[N1kh1l · Accepted solution]

fffma

I am assuming your initial value of 20 is on PRICELIST member L01. Something like below should work. Just adjust as per your model and dimension name. You can change the MEMBERSET for the Date as per your planning horizon.

MEMBERSET [d/Date] = "202301” TO "202312”
MEMBERSET[d/Measures] = "LIST"
FOREACH [d/Date]
IF DATEDIFF([d/PRICELIST].[p/VALID_FROM], [d/Date], “MONTH”) >= 0 AND DATEDIFF([d/Date], [d/PRICELIST].[p/VALID_TO], “MONTH”) > 0 THEN
DATA()=RESULTLOOKUP ([d/Date]="000000")
ENDIF
ENDFOR

Hope this helps !!

Br.

Nikhil

[fulmil · Accepted solution]

Hey Nikhil, this is awesome!

we tweaked it a little bit based on further requirements but works as gold.

thanks again.

[zeenat_17]

Hi,

I am facing the issue with my Weekly based Planning model.

My Version dimension has two property "Start Year" and "End_Year" of Type Text

e.g. -> FW_202338 should forecast from W38 to W43

I am trying to write the data action by scoping my date dimension to just work for 1st 2 Weeks i.e. 202338 and 202339 as follows with the help of the NEXT function, but it is giving me the following error.

The data action works fine if i provide the entire range as seen below. but I have a specific calculation that should only work for 1st 2 weeks hence I am looking to use the Next Function but unable to do so.

can you please help.