November 6, 2007 at 10:22 am
I am tryring to create a Previous Month Date Filter for my model in my time dimension.
Does anyone one have steps for building it into the model? (The MSDN tip did not clue me in.)
Or where I could get them?
Or why it does not do it automatically, like some other business intelligence tools?
Thanks!
November 8, 2007 at 9:55 am
Just to expand I am looking to present the following for any particular month. :
Reporting on January 2007
MTD-JAN 2007 MTD-DEC 2006 YTD-JAN 2007 YTD-JAN 2006
November 9, 2007 at 7:56 am
Again folks, add some more details. It's tough to respond unless there's some meat on the bones.
I'll assume you're looking for the MDX syntax to reference previous month. If not, let me know what you're looking for an I'll try to help.
First, if you want to "automatic" timeseries functions (like MTD) in MSAS, you have to make sure you indicate in the time dimension which attributes represent month (in the meta data). The documentation doesn't really make this dependency clear, but once you understand the principal it's obvious. (Hey, isn't it always.)
Other than clarity (and possible portability between disparate models), I don't see a particular benefit in these functions, but I'd be interested in other perspectives / opportunities others have determined.
As well, you must have a hierarchy, to group days, months and years. Again, not explicit, but obvious once you think about it.
Alot actually depends on the context you're using, but roughly, you could use
[Time].[myMonthAttribute].CurrentMember.PrevMember
Now, before we jump to trying to use MTD, it's important to understand this function is probably misnamed. It really should be named "Months to date" (or MsTD), since it returns a list of months within the current year. (I believe you're looking for the list of days within the month.)
Let's assume you've defined the following hierarchy:
[Time].[by Calendar] as
[myYearAttribute]
[myMonthAttribute]
[myDayAttribute]
To get your month to date, use the following:
Aggregate(
PeriodsToDate( [Time].[by Calendar].[myMonthAttribute],
ParallelPeriod( [Time].[by Calendar].[myMonthAttribute], 1, [Time].[by Calendar].[myDayAttribute].CurrentMember )
)
)
And yes, Microsoft probably should add a function DTD (Day to Date), to do this directly. Or someone could simply add an assembly to MSAS and hopefully post it for the rest of us. Might do that myself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply