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.