.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

MDX Last Year

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

WITH MEMBER [Measures].[FutureRevenue] AS
([Date].[Hierarchy].CurrentMember.Lag(1),[Measures].[Revenue])* 1.25

SELECT [Measures].[Sales] ON 0,
[Date].[Hierarchy].[Year] ON 1
FROM CubeName

How do I get the above script to look back one year regardless of the MemberLevel. Currently, if the CurrentLevel is month, it goes back one month and if the currentlevel is year it goes back one year. I want to go back one year regardless of the member level. For example, for months it should go back one year and look at the corresponding month.

Thanks in advance.

View Complete Post

More Related Resource Links

Editor's Note: A New Year-A Fresh Perspective


The results of our reader survey are in. This month, Howard Dierking shares the takeaways.

Howard Dierking

MSDN Magazine January 2008

Editor's Note: A New Year-A New Vista


Josh Trupin revs the engine to get us all ready for Windows Vista.

Joshua Trupin

MSDN Magazine January 2007

Editor's Note: Celebrating Our 20-Year Milestone


The world of software development is all about milestones. At the lowest level, a programmer has to create a certain set of functions in a fixed amount of time, and is then judged on this performance.

Joshua Trupin

MSDN Magazine January 2005

Calculating Full Year Total

Im creating a sales report in SSRS by pointing it at a cube in SSAS.  Im creating calculated members to aggregate my data in various ways so they can be shown on the report.   I have a FACT table containing sales figures and various dimension tables including a server created time dimension.   Ive managed to create pretty much everthing I need (e.g. Current YTD, Prior year YTD).  What I now need is a full year figure to compare current year sales to.   Below is what Im aiming for i.e. the total of 122712 from 2009 appearing against every month in 2010.   Fiscal Year Fiscal Month Count Full Prior Year sales 2009 Apr-09 9805     May-09 9942     Jun-09 8986     Jul-09 9696     Aug-09 8912     Sep-09 10097     Oct-09 11713     Nov-09 10434     Dec-09 8820     Jan-10 10821     Feb-10 11844     Mar-10 11645     Total 122712   2010 Apr-10 3517 122712   May-10 1669 122712   Jun-10 2518 122712   Jul-10 3306 122712   Ive been bashing my head against a brick wall with this one so any help would be gratefully received!

Quater and Year(Urgent)

  The following metric values are dependent upon the Completion data parameter.  Actually, a one to one mapping exist.   Metrics Parameter Count of valid credentials earned by Month Completion Date Parameter Current Month Count of valid credentials earned by Quarter Completion Date Parameter (options include) FY-Q1 FY-Q2 FY-Q3 FT-Q4 Count of valid credentials earned FYTD (Oct 1 = FY start) Completion Date Parameter Fiscal Year     how can i create the Multi select and how can i write the query  in dataset for feting the data from data base table .By defalut current month is selectd in multiselect plz let me know

Date format for Year, Year-Month, and Date (Year-Month-Day) where input is not known

I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known. We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date. I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something. For sorting, when records have same Year, I would have another column to do relative sorting... So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any. Thanks in advance. --Sharad 

How I select Start of week is Friday from date of Year ?

Hello everyone, I am tring to solve How i select Start date is Friday from week? Details for which i want to show I have dtmDate column in a query. from that date i display week of that date as per using (ww,dtmDate) as dtmWeek, and parameters are StartDate & EndDate Suppose I select StartDate as 1/1/2007 & End Date as 12/31/2007   I  do not want to show Output as: Weeks                      Column1          Column2 1/1/2007 (Thuesday)       5                     6 1/2/2007 (Wed)               4                     2 | | 1/5/2007  (Friday)            1                      8 1/6/2007 (Saturday)        7                     

Display the Sum of hours and Month Year using sql Query

Hi,  I have,hours(in varchar),Id in table tbl_x ,i need the sum of hours, monthyear(eg. mar 2010)for the last three months.But with two separate queries i am getting these results.but actually i need in the following format   Month/Year      TotalHours-----------------    ----------------Mar 2010            0400Apr 2010             0450-----------------------------------to get month/Year i am using this querySELECT right(convert(varchar, fromdate, 106), 8) from tbl_x where id='11101' for the sum of hours SELECT SUM(CAST(hours AS INT))AS TOTALHRS FROM tbl_x WHERE id='11101'

report expression for month and year

Hi , Can anyone help me how to write a report expression to get the following out put? Actually this value is the month of previous month. I want to set default month name of the parameter to previous month. This is sep.So it should display like this; Aug - 10 Thanks shamen

Calculate Fiscal Year

HI Experts,  I need help in calculating Fiscal year for given Date.  My fiscal year begins on Oct 1st of every year and I also need to calculate Fiscal Day number of year,  EX: if Date in table is 2010-10-5 then my fiscal year start date should be 2010-10-01 and in my next column I should get day of year as 5    any help is greatly appreciated.  thanks, vishal   

Leep Year Issue

I use the following to get the start of a year period, it might be a fiscal or calandar year.Date.Parse(dtrSurveyData.Item("EndDate1").ToString()).AddDays(-364) I'm just setting a label to this date. If the user enters 12/31/2008 as the enddate1 it shows as 1/2/2008, because it was a leep year. I cannot just use the "1/1/" becuase they might be entering 7/1/2009 as the end of the fiscal year period. Any suggestions on how to handle the leep year?

YEAR --WEEK of the YEAR -- DAY of the WEEK to datetime

Good Evening Can someone help me covert a string in the format YEAR --WEEK of the YEAR(1-53)  -- DAY of the WEEK(1-7)to datetime ie 2010-38-6 is today = '2010-09-17'  valid sql server date nb day numbers are 1 to 7, 1= sunday thanks   David          

List of Month and Year in SSRS report parameter?

Hi, I have two parameters in my SSRS Report i.e. Month and Year. I want to show the list of all Months in dropdown and years for a particular range say 1995-Current Year.
Can any one suggest me the query for how to get it to work.
Thanks!! MCP

Carry Value from user list on one page to year list on another page

How can I pass a value from user list on one page to year list on next page without coding. Is there any way. Thanks in advance for the help.

Substract Value of Year 1 from Year 2



I have a SQL - Statement that returns amount, year and month and that is grouped by month and year. The date range is between two years. Thus the result contains both years, divided into months and the according amounts.

In a matrix I want to compare the amounts of the months of both years. The columns are grouped by months and are automatically repeated. The rows are grouped by year. Because I have 2 years in my range, 2 rows are generated automatically.  Now I want to substract the amount of year 2 from year 1 (or vice versa, it doesn't matter).

My question is: How can I refer to a certain year - let's say 2009 - if the rows are only created when the report is executed. Is there a way to work with a scope?

My former solution was to make two querys, each getting the amount of one of the years and then combining the results with a union. I had 2 separate fields, Amount2009 and Amount2010. In the first query Amount2009 was filled in, whereas I set Amount2010 to 0. In the second Query I did the same, just the other way round. Then I grouped again by month and year and had 2 Amount, that I substracted. That got quite complex, with more than just one value field.

Sorry if my post is incomprehensible. If you have any questions, just ask.

Any help is greatly appreciated

Thanks in Advance

MDX for Prior Year


Hi All,


I have a measure called Actual Hours in my cube. I need to create a calculation in the cube for Budget Hour which needs to be something like

Budget Hour = (Last Year's Actual Hour)*1.25 and when I drill down(to say Month level) it should be Budget Hour = (Last Month's Actual Hour)*1.25 etc.

Any idea how to go about this? Any help would be truly appreciated. Thanks in advance.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend