For a single value, you can use:
=Sum(if([INITIATION_DATE] <= '$(vSingleDate)' AND [EXPIRY_DATE] > '$(vSingleDate)', [Amounts]))
Let vSingleDate = '31/12/2013';
I am not clear what you would be comparing when using multiple date values. Do you want to compare the min and max values in some way, or do you want to do the single expression calculation, but applied to each date in turn, summing the total?
Thank you Jonathan,
For multiple dates, I would be looking at performing the above calculation for a range of dates:
vDateValues = '31/12/2013', '31/12/2012', '31/12/2011', '31/12/2010'
so I would have a chart to show me valid total stock values as at the end of the years in the range 2010-2013.
I'm just not sure how best to incorporate the string of dates into the expression and how best to interface with the user. Perhaps this could be achieved though a multiple selection box with pre-defined end of year dates.
Then you would probably find it easier if you use dates in a field, such as the end year date as you suggest. Then the dimension could be the compare date (for example CompareDate) and the expression could be:
=Sum(if([INITIATION_DATE] <= [CompareDate] AND [EXPIRY_DATE] > [CompareDate], [Amounts]))
CompareDate should be in an island table (no association to the rest of the model. The users could make selections in CompareDate to see specific values and you could make the calculation conditional on the users having selected between one and a maximum number of compare dates (eg 12; to avoid killing your server by trying to calculate for all dates at once)
=GetSelectedCount(CompareDate) >= 1 And GetSelectedCount(CompareDate) <= 12