March 2, 2015 at 2:56 am
Hi
I have been trying to Sum a measure (Income) over the next 12 months (Date) for a specific sales month (SalesDate)
This query at least returns values but does not limit the measure to the first 12 months:
With
Member [Measures].[Income - plus 12] As
Iif(
[Date].[|Date - Month Description].CurrentMember>=[SalesDate].[SalesDate - Month].CurrentMember
, Sum(LastPeriods(- 12
, [Date].[Date - Month Description].CurrentMember
)
,
[Measures].[Income]
) HINT STRICT
, Null
)
Select
{
[SalesDate].[SalesDate].&[2014]
*
Except([SalesDate].[SalesDate - Month].[SalesDate Month]
, [SalesDate].[SalesDate - Month].[All].UNKNOWNMEMBER
)
} on Columns
, [Measures].[Income - plus 12] on Rows
From [My Cube]
;
So I tried this query but it returns nulls:
With
Member [Measures].[Income - plus 12] As
Sum(
(LinkMember([SalesDate].[SalesDate - Month].CurrentMember
, [Date].[Date - Month Description]
)
:
LinkMember(
ParallelPeriod([SalesDate].[SalesDate - Month].[SalesDate Month]
, -12
, [SalesDate].[SalesDate - Month].CurrentMember
)
, [Date].[Date - Month Description]
)
,Root([SalesDate])
)
,
[Measures].[Income]
)
Select
{
[SalesDate].[SalesDate].&[2014]
*
Except([SalesDate].[SalesDate - Month].[SalesDate Month]
, [SalesDate].[SalesDate - Month].[All].UNKNOWNMEMBER
)
} on Columns
, [Measures].[Income - plus 12] on Rows
From [My Cube]
;
I would appreciate any input (urgently as I have been fighting with this for a week now)
March 18, 2015 at 4:57 pm
Something like this would work:
aggregate
(
{[Date].[Fiscal Date Hierarchy].currentmember : [Date].[Fiscal Date Hierarchy].currentmember.lead(11)}
,[Measures].[Sales]
)
Note that this would include the current month, and the next 11 months...making it 12 in total.
If you need to exclude the current month, it would be as simple as this:
aggregate
(
{[Date].[Fiscal Date Hierarchy].currentmember.lead(1) : [Date].[Fiscal Date Hierarchy].currentmember.lead(12)}
,[Measures].[Sales]
)
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply