The logic is really good, but the thing is i have more dimensions apart from just date, so if I merge AsofDate with each parameter, the table will be too huge.
The count for the dimensions are
Item - 1 million.
Months - 48
Salesperson - 200
Divisions - 20
I hope you understand what I want to say.
Do you think creating a table will be better then using the logic suggested by you in script?
Thanks again for a wonderful logic.
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.