Fact: Census
-------------
- Census count (values 1 or 0)
- CalendarKey (dates in the format "yyyymmdd")

Dimension: Calendar
----------------------
- CalendarKey(dates in the format "yyyymmdd")


How do I get a calculated member "OpeningBal" (in psuedocode)
"Opening Balance = Sum(Census Count - where Calendarkey <= user entered date
)?"

I tried creating a Named Set called "CalendarDates" but could not use it to
get the dates <= user entered dates.

Any help is greatly appreciated.
--
Thank you,
Ashok G

Assuming AS 2000, a cube measure: [CensusCount], and user selection of a
date on the Calendar dimension, the MDX for the calculated measure:
[OpeningBal] could be like:

Sum(PeriodsToDate([Calendar].[(All)]),
[Measures].[CensusCount])



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thank you for your prompt response. The PeriodsToDate() function returns the
sum(Census count) only upto a partilular [Month] level in the Calendar
dimension. I provided the level as
SUM(PeriodsToDate([Calendar].[Calendars].[(All)]), [Measures].[CensusCount])
Any thoughts why it would not work for a specific [Date]?

Below is my calendar dimension attributes:
- Group (values "Calendar" or "Fiscal")
- Year (1978, 1979, ....)
- Quarter (1,2,3,4)
- Month (1,2,3,4..12)
- Week number (1,2,3,....)
- Date (yyyymmdd values---displays alternate attribute in format "April 1,
2006")

The above attributes are also part of a user heirarchy ("Calendars") as
Group->Year->Quarter->Month->Week number->Date.

Thanks you so much for your help. As always I really appreciate it.

--
Thank you,
Ashok G


"Deepak Puri" wrote:

Since it sounds like you're using AS 2005, you might try explicitly
specifying the Hierarchy current member, like:

Sum(PeriodsToDate([Calendar].[Calendars].[(All)],
[Calendar].[Calendars].CurrentMember),
[Measures].[CensusCount])

(assuming that the date is selected on this hierarchy).


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,

Yes I am using AS2005.
Tried as you suggested and I explicitly declared the heirarchy and used the
heirarchy in the Dimension filter expression. I am able to get some results
only as long as I navigate and select upto the [Month] level in the heirarchy
(heirarchy is Group->Year->Quarter->Month->Week number->Date). The results
become 0 as soon as a enter a value for either the [Week number] level or the
[Date] level. Strange huh!

I have verified that the data exists in the underlying table for the filter
condition for the [Date] so I am at a loss as to why it does not return any
values after [Month].

Can you please help?


--
Thank you,
Ashok G


"Deepak Puri" wrote:

Ashok,

It's hard to say without looking at the Attribute Relationships in your
[Calendar] dimension, but I suspect that there may be an issue with the
modelling of weeks. Unless you're using an accounting calendar, weeks
don't roll up exactly to months - they may span 2 months. Will it work
down to the day level, if you remove weeks from the Calendar hierarchy?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

I finally figured out the issue. There were a couple places where I was
headed in the wrong direction. Basically I was placing the filter condition
in the SubCube filter area of the BI studio. Also I used the PeriodsToDate()
as follows:

SUM(PeriodsToDate([Calendar].[Calendars].[Group], [Measures].[CensusCount])

That did the trick.

Thanks for helping me though clarify my thoughts and understanding


--
Thank you,
Ashok G


"Deepak Puri" wrote: