MDX new measure question

Robin77 1 Reputation point

I have a measure [Measures].[Value]
and dimensions:

I need to create a new measure that would NOT contain the following set of data:

Excluding Company A and months 1 and 2
Excluding Company B and months 2 and 3

I will create a new measure in Excel, there are Olap Tools and an editor for the new measure.

Can anybody help me to write it in MDX? A polite request for help

Thanks in advance

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points

    You probably won't believe the amount of hassle these requirements actually cause.

    You'll have to create two sets, one of and the other of month.month.month, making sure to check the 'recalculate...' checkbox. If you don't, many filtering operations on any of the dimensions will ruin your result.
    The measure in its most generic form will be
    aggregate ( existing ( company_set * month_set - (, month.month.1) - .... - (, month.month.3) ) , measures.value )

    And, you'll still have more fine-tuning to do if, for example, Value happens to be a calculated measure.

    Excel and MDX are a fun combination :)

  2. Robin77 1 Reputation point

    Interesting. Thank you for Your help, give some time to check it

  3. Robin77 1 Reputation point

    set expression is invalid
    I think so

    Aggregate(Set_Expression [ ,Numeric_Expression ])

    0 comments No comments