Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))