I am using the following MDX query to return same day last month values:
WITH
MEMBER
[Measures].[Same Day Last Month] AS'(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month],1),[Measures].[Active Account Indicator])'
SELECT
{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]}
ON COLUMNS, NON EMPTY {[Reporting Date].[Date].Members} ON ROWSFROM
[Financial]
This works for most days. However, for the last day of the month this cannot work when the current month has more days than the previous month. For example, on March 29, 30 and 31, the query will return NULL for [Same Day Last Month], since February 29, 30, 31 is an invalid date.
I am sure others have run into the same problem. What is the best way to solve this
I'm grateful for any insights.
Dan