MS SQL Server OLAP - Calculating the sum upto the previous period dynamically.

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

Calculating the sum upto the previous period dynamically.

dravid20@hotmail.com

2005-11-23, 8:24 pm

For example if I have 2 dimensions
Time ( year-->MOnth---> Day)
Customers
and a measure

How can I create a formula to dynamically calculate the sum of the
Unit sales for the customers for this yr upto last
month(october)...current month is November...

so for example if the current month is November, I will like to add the
sum of all the months upto october for this year..(this should be
dynamic since when december comes the sum should be upto November)

Any Ideas?

Thanks,

Mahesh Shrestha

2005-11-23, 8:24 pm

Hi

I think you can sum up the measure for following date range.

Ancestor([Date].CurrentMember, [Month]).FirstSibling :
Ancestor([Date].CurrentMember, [Month]).Lag(1)

This should calculate the measure upto last month dynamically.

Thanks
Mahesh

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links