MDX - Filter different measures using different date intervals

Question

This is similar to another question I made (MDX - Running Sum over months limited to an interval) but I feel that I was going off track there. Let me start again. I have a calculated measure

MEMBER [Measures].[m_active] AS ([Measures].[CardCount], [Operation].[Code].[ACTIVATION])

That I want to filter on a short interval (let's say from 10 January 2016 to 20 August 2017, those are parametrized)

and another calculated measure that i want to filter since the beginning of date dimension (1st January 2010) to the end of last filter (20 August 2017 in this case), this measure is running sum of all the precedent

MEMBER [Measures].[tot_active] AS (
    SUM({[Calendar.YMD].[2010].Children}.Item(0):[Calendar.YMD].CurrentMember, ([Measures].[CardCount], [Operation].[Code].[ACTIVATION]))

On the columns I have this calculated dimensions and on the rows I have months (in the small interval range) and another dimension crossjoined

SELECT
    {[Measures].[m_active], [Measures].[tot_attive]} ON COLUMNS,
    NonEmptyCrossJoin(
        {Descendants([Calendar.YMD].[2016].[Gennaio]:[Calendar.YMD].[2017].[Agosto], [Calendar.YMD].[Month])},
        {Descendants([CardStatus.Description].[All CardStatus.Descriptions], [CardStatus.Description].[Description])}
     ) on ROWS

If I put a date range in the WHERE clause the first member is perfect but i ruin the second, how can I make the second member ignore the WHERE clause? Or is there another solution?

What for do you need months? Do you want to show only months within the current measure day range? — Danylo Korostil
Yes on the rows I have months -- CardStatus -- measures but the interval is to the day (so the fist and last month are bounded), anyhow I found a solution by including "all dates" in the calculated member of the running sum from the beginning of time and by defining the desired interval for the other measures in the WHERE clause — Crysis85
I meant if you set 20 August, you should get the value of sum of 1-20 days for August, not entire month value, right? If you solved it yourself, it doesn't matter. — Danylo Korostil

whytheq whytheq · Accepted Answer · 2017-09-13T23:08:13

Without testing I'm a little bit unsure of the behaviour, but did you try moving the filter from a WHERE clause into a subselect?

Subselects are formed like this:

...
FROM (
   SELECT 
      <date range for filter> ON 0
   FROM cubeName
)

MDX - Filter different measures using different date intervals

1 Answers