MDX Previous YTD on fiscal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

MDX Previous YTD on fiscal

Author

Topic

teg
Starting Member

14 Posts

Posted - 2010-06-03 : 10:05:51

Any Idea why this MDX is not working and bringing zeros accross all levels.

it should show the last Fiscal year to date based on the current member. i.e. if Im looking at may 2010, I should get May 2009 year to date (In this case april 09 - may 09) etc.,

It works fine without the parallelperiod in for this YTD. Its annoying me now... Please Help.. thanks

CREATE MEMBER CURRENTCUBE.[Measures].[LY YTD Invoiced Value]
AS
Sum(PeriodsToDate(ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Year],1),[Time].[Fiscal Calendar].[Fiscal Year].CurrentMember), [Measures].[Invoiced Value])
FORMAT_STRING = "Currency",
VISIBLE = 1;

savior faire
Posting Yak Master

194 Posts

Posted - 2010-06-03 : 11:31:10

In many programming languages when you have complex functions, statements and/or calculation, it helps to break each element down to see if it works as intended.
I would first see if the "ParallelPeriod" function returns the correct result. If okay, then move to the "PeriodsToDate" function, and so on.

______________________________________________
Talk sense to a fool and he calls you foolish.

savior faire
Posting Yak Master

194 Posts

Posted - 2010-06-03 : 11:36:08

I am looking at your statement, I am not an mdx expert, but it seems to me that in the "PeriodsToDate" function, this part of it: [Time].[Fiscal Calendar].[Fiscal Year].CurrentMember, should have a reference to the current month, and should be something like this: [Time].[Fiscal Calendar].[Fiscal Year].[Month].CurrentMember

______________________________________________
Talk sense to a fool and he calls you foolish.

teg
Starting Member

14 Posts

Posted - 2010-06-03 : 11:49:06

Hi,
I have broken it down. the following works fine for the same month last year (not YTD), its when I add periodtodate for the YTD. This should work as far as I can see.

CREATE MEMBER CURRENTCUBE.[Measures].[LY Invoiced Value]
AS ([Measures].[Invoiced Value],ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Year],1)),
FORMAT_STRING = "Currency",
VISIBLE = 1 ;

teg
Starting Member

14 Posts

Posted - 2010-06-03 : 12:00:23

Hi I tried changing to [Time].[Fiscal Calendar].[Fiscal Year].[Month].CurrentMember with the same outcome. I believe this part is optional anyway and not needed (see example below). Here is this year to date that works great.

CREATE MEMBER CURRENTCUBE.[Measures].[YTD Invoiced Value]
AS
(sum(periodstodate([Time].[Fiscal Calendar].[Fiscal Year]),[Measures].[Invoiced Value])),
FORMAT_STRING = "Currency",
VISIBLE = 1;

It could have something to do with using a fiscal calendar. Im not entirely sure, but I havn't seen an example of this working yet.

savior faire
Posting Yak Master

194 Posts

Posted - 2010-06-03 : 14:52:57

Here is the answer to your problem:

http://www.ssas-info.com/analysis-services-faq/27-mdx/110-how-can-i-get-last-previous-year-to-date-ytd-values

______________________________________________
Talk sense to a fool and he calls you foolish.

teg
Starting Member

14 Posts

Posted - 2010-06-04 : 04:31:53

Thanks saviour faire, but The Ytd function is a shortcut function for the PeriodsToDate function where the Type property of the attribute hierarchy on which the level is based is set to Years. That is, Ytd(Member_Expression) is equivalent to PeriodsToDate(Year_Level_Expression,Member_Expression). Note that this function will not work when the Type property is set to FiscalYears.

My Time hierarchy is set to fiscal years.

teg
Starting Member

14 Posts

Posted - 2010-06-04 : 06:38:15

If anybody interested. this is a work around. I had to use an IIf to only show the level "fiscal Month" I needed as this was causing an error on any levels lower. Its not very elegant but it works. Like most things there is always another way. In this case I find the opening month of the last year and use a range between that and the same month last year.

CREATE MEMBER CURRENTCUBE.[Measures].[LY YTD Invoiced Value]
AS
iif([Time].[Fiscal Calendar].currentmember.level.name = "Fiscal Month",
Sum(OpeningPeriod([Time].[Fiscal Calendar].[Fiscal Month],
[Time].[Fiscal Calendar].currentmember.parent.PrevMember)
:
ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Year],1)
, [Measures].[Invoiced Value]),null),
FORMAT_STRING = "Currency",
VISIBLE = 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources