My Photo

Consulting/Training

Speaking engagement

January 2007

Sun Mon Tue Wed Thu Fri Sat
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

My Online Status

Disclaimer

  • No Warranty..
    I have taken care in writing the articles on this blog and make no implied or expressed warranty of any sort. For any errors or omissions, I will not be responsible in any way.

« mdx query: LASTPERIODS | Main | mdx query: Slicer »

August 30, 2006

Comments

Mike Mahoney

Hi Zulfiqar

I found the periodtodate example very helpful as this syntax has changed from AS2000. The example accumulates within each year. How would you change it to accumulate over the complete span of the calendar (multi-years)?

thanks
Mike

Josh Ashwood

That can easily be done with the All member :-
with member measures.ptd as
sum(
periodstodate(
[Date]
.[Calendar]
.[(All)]
,
[Date]
.[Calendar]
.currentmember
)
,[Measures].[Reseller Sales Amount]
),format_string = "currency"

select
{
[Date].[Calendar].[Calendar Quarter].&[2003]&[2]
,
[Date].[Calendar].[Calendar Quarter].&[2003]&[3]
,
[Date].[Calendar].[Calendar Quarter].&[2004]&[1]
} on 0,
{
[Measures].[Reseller Sales Amount]
--,measures.yearago
,measures.ptd
} on 1
from
[Adventure Works]

KB

How would you change it to accumulate over the current and future years (as in forecasts), excluding all till last financial years? i.e. starting at the present financial year and going upto future years?

The comments to this entry are closed.