I need help regarding below scenario. Hope someone will have solution for the same.
I have a variable which holds a value say 2000.
I have a table which has the transactions by date, something like shown below.
The Date format is MM/DD/YYYY.
What i want is if I pass the Date say 1st Mar 2013 then it should add the quantity from 1st Mar 2013 and going in descending order and check for the sum of quantity and stop on the date where the sum of quantity from 1st Mar 2013 to that day is lesser than the variable quantity.
I don't think you need to merge AsofDate with each parameter, only with the date field. In the example I joined the AsofDate with the main table, but that is not really necessary. You can simply keep it as a separate table linked to the data field.
If you have 48 months then I assume that means about two years. That would mean about 730 unique date values. The AsofDate table would at contain at most (730^2+730)/2 = 266815 records. That's not all that large. And it will compress quite nicely since there are only 730 unique values.
If you add the dimensions to your chart then you should get the correct results from the expression for the dimension combinations.