MDX TTM Calculated member - day level

Question

I need to create a calculated member that calculates revenue for TTM (Trailing Twelve Months) associated to selected date (day level).

I tried something like this:

SUM(
    {
        [Accounting Effective Date].[Date Hierarchy].CurrentMember.Lag(365)
        : [Accounting Effective Date].[Date Hierarchy].CurrentMember
    },  
    [Measures].[Revenue]
)

But this doesn't work with leap year, for example if I pick 2013-01-01 than it returns 2012-01-02.

I have also tried this but this one is also not good since it takes first day of the month:

SUM(
    {
        [Accounting Effective Date].[Date Hierarchy].CurrentMember.Parent.Lag(12).FirstChild 
        : [Accounting Effective Date].[Date Hierarchy].CurrentMember
    },  
    [Measures].[Revenue]
)

Date hierarhy is following: Year > Quarter > Month > Date

do you want revenue sum for last 12 month from a particular date? or do you want every year´s sum revenue in twelve month´s interval? — daniel_aren
Please define "TTM" in this context. Acronym tags are evil... — Charles
I want to be able to calculate revenue for trailing twelve months (TTM) for selected date. For example, if I pick 2013-04-05 starting point should be 2012-04-05 — ilija veselica
Give a try to the Cousin function in MDX msdn.microsoft.com/library/ms145481.aspx It will be up to you how to consider the cousin of Feb 29th in a non leap year — michele

MoazRub MoazRub · Accepted Answer · 2019-02-16T19:14:35

the following query can help, it uses cousin function to return last years date.

with member [Measures].[TestValue] as 
(cousin([Date].[Calendar].currentmember,[Date].[Calendar].currentmember.parent.parent.lag(1)),[Measures].[Internet Sales Amount])
member [Measures].[TestDate] as 
cousin([Date].[Calendar].currentmember,[Date].[Calendar].currentmember.parent.parent.lag(1)).item(0).name
select
{[Measures].[Internet Sales Amount],[Measures].[TestValue],[Measures].[TestDate]}
on columns,
{
[Date].[Calendar].[Date].&[20130922]
}
on rows from 
[adventure works]

MDX TTM Calculated member - day level

1 Answers