Calculating Inception-to-Date Measures
A requirement popped up for calculating an inception-to-date (ITD) measure.
MDX CALCULATED MEMBER
Aggregate(PeriodsToDate ([Date].[Calendar Hierarchy].[(All)]), [Measures].[Sales Amount])
In MDX, ITD is not much different than other time calculations, such as YTD or QTD. We only need to specify that the calculation will be performed over the top-most level of the Date hierarchy whose default name is ([Date].[Calendar Hierarchy].[(All)]. Of course, time calculations should be added to a shell dimension and a scope assignment can be used then in the cube script:
Scope (
[Accounting Date].[Calendar Year].[Calendar Year].Members,
[Accounting Date].[Date].Members ,
[Relative Date].[ITD]
);
this =
Aggregate
(
{[Relative Date].[PTD]} * PeriodsToDate ([Date].[Calendar Hierarchy].[(All)])
);
End
Scope;
DAX MEASURE
=CALCULATE(SUM(‘Reseller Sales'[Sales Amount]), FILTER(ALL(‘Date’), ‘Date'[Date] < MAX(‘Date'[Date])))
In DAX, we pass a table consisting of the dates we need to aggregate over as a second argument of the CALCULATE function. We need to use the ALL function to ignore the row context of the Date table in order to calculate the date range by filtering the date table until the current period. The MAX function is used to get the last date of the current period on the report, e.g. 10/31/2012 if the month is October 2012.
