Friends i have a very unusual situation , please help me out i have requirment , which should give the average of 3 months prior , when i use lag or last periods command option, it does calculate average on month basis, but i need to calculate average on day wise for example if i am on 15th day of the month , it should calculate the average on last two months and 15 days which should be average of 75days Average on 30days(1st Month)+ 31days(2month)+ 15 days (of current month) but apparently lag either takes me at month level or day level. i need to have 3month average on a day level currently i am using command , AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Q1_Avg] ) but that takes me lag on 2 month not a date wise. if i use a lag of 90 days , it takes 90 days behind but not average of 75 days as per scenario please suggest
This is one one the very rare situations where you might consider using a cursor. Search this site for an article by Mirko Marovic. He wrote about that some time ago.
The Article of Mirko Marovic is on T-SQL Cursor. I have requirment that i need to take care of in MDX