title: List Dimension Members if selected date falls between Start Date and End Date in fact records SSAS MDX
body: <p>I have a fact table that contains invoice line items, and since these line items are subscriptions, there is a Start Date and an End Date involved</p>
  
  <pre><code>LineItem Customer       Product         OrderDate       StartDate       EndDate
  1        Customer A     Product A       1/1/2013        1/1/2013        3/1/2013
  2        Customer A     Product B       1/1/2013        1/1/2013        4/1/2013
  3        Customer B     Product A       1/1/2013        2/1/2013        6/1/2013
  </code></pre>
  
  <p>The client wants a <strong>list</strong> of Active Customers for a selected date in Excel(PivotTable). They want to select a date, and if the date falls between the Start Date and End Date of any Invoice Line Item record, then the Customer should be displayed.  For example:</p>
  
  <p>If '1/5/2013' is selected, the Customer List should return (LineItem: 1, 2):
  Customer A</p>
  
  <p>If '2/10/2013' is selected, the Customer List should return (LineItem:  1,2,3):
  Customer A
  Customer B</p>
  
  <p>If '5/15/2013' is selected, the Customer List should return (LineItem:  3):
  Customer B</p>
  
  <p>Next, the client wants to filter by Products as well, so:</p>
  
  <p>If '3/20/2013' is selected and Product A is selected, the Customer List should return (LineItem: 3):
  Customer B</p>
  
  <p>In SQL this is very easy:</p>
  
  <pre><code>Select Distinct Customer from Fact where @SelectedDate between StartDate and EndDate
  </code></pre>
  
  <p>I am unsure on how to approach this problem in SSAS and what to do with the 'Selected Date' as in, should this be another dimension?  if so how is it going to relate to the Fact Table?</p>
  
  <p>Or can this be done on Excel/PowerPivot side using in some other way?</p>
  
  <p>Also my initial approach is to create a Named Set of customers - but I am not sure how to create it based on date range etc.</p>
  
  <p>Any help will be appreciated!
  Thanks </p>