Implementing the logical AND on members from the same hierarchy

This recipe shows how to implement the AND logic using members from the same hierarchy.

In the Adventure Works DW 2016 database, there are two members, [New Product] and [Excess Inventory], in the [Promotion Type] hierarchy:

[Promotion].[Promotion Type].&[New Product] 
[Promotion].[Promotion Type].&[Excess Inventory] 

These two promotion types have reseller orders, but the only two months in which they both have reseller orders are January and December.

The idea is to have a single query that displays the reseller orders, where both promotion types occur in the same month. In other words, we want to show the reseller orders for January and December.

Our goal is to somehow combine these two members from the same hierarchy so that we perform the logical AND along the [Month of Year] hierarchy on the Date dimension.

Getting ready

Start a new query in SSMS and make sure that you're working on the Adventure Works DW 2016...