Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Year to Date Totals with MDX in Analysis Services.

Posted on 2011-10-14
1
Medium Priority
?
1,042 Views
Last Modified: 2016-02-15
I have a simple demonstration cube with one dimension, a time dimension with levels date, month, quarter and year,   and a measure [ACCOUNT BALANCE].

I'm trying to compute Year to date totals for [ACCOUNT BALANCE].
To do this I created a calculated measure called [YTD ACCOUNT BALANCE]

WITH MEMBER Measures.[YTD ACCOUNT BALANCE] AS SUM (YTD([TIME].[DATE].CurrentMember),[Measures].[ACCOUNT BALANCE])
SELECT    NON EMPTY({[Measures].[ACCOUNT BALANCE], Measures.[YTD ACCOUNT BALANCE] } )ON COLUMNS
,[Time].[Date].&[2005-12-06T00:00:00]:[Time].[Date].&[2006-02-26T00:00:00] on Rows
from [BalanceData]

This query generates an error.  The error says,

By default a year level was expected. No such level was found in the cube.

See attached image. Shows cube structure, query, and output
0
Comment
Question by:cipriano555
1 Comment
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 2000 total points
ID: 36973303
Change the 'Year' attribute type to "Years". it could be on Regular now.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question