My MDX calculation doesn't work in Excel when multiple filters are selected (query context)

I've got this function which is used to get the closing values for stock on hand in a SSAS cube. My problem is, is that this doesn't work when in Excel, multiple items are selected.

From what I've read, this is to do with Excel using Subcubes and the query context not being correct. I've seen that Dynamic Sets are the way to fix this, however I have no idea how to implement this.

WITH MEMBER [Measures].[Closing Stock Date] AS IIF( Ancestor( StrToMember('[Date].[Retail].[Date].&[' + Format(Now(), 'yyyy-MM-dd') + 'T00:00:00]'), [Date].[Retail].CurrentMember.Level ) is [Date].[Retail].CurrentMember, StrToMember('[Date].[Retail].[Date].&[' + Format(Now(), 'yyyy-MM-dd') + 'T00:00:00]'), ClosingPeriod( [Date].[Retail].[Date], [Date].[Retail].CurrentMember ) ).Name

SELECT
  {
    [Measures].[Closing Stock Date]
  } ON COLUMNS
FROM
(
  SELECT ({
    [Date].[Retail].[Retail Period].&[Period 1, Ret 2014],
    [Date].[Retail].[Retail Period].&[Period 2, Ret 2014],
    [Date].[Retail].[Retail Period].&[Period 3, Ret 2014],
    [Date].[Retail].[Retail Period].&[Period 4, Ret 2014],
    [Date].[Retail].[Retail Period].&[Period 11, Ret 2014],
    [Date].[Retail].[Retail Period].&[Period 12, Ret 2014]
  }) ON COLUMNS
  FROM
    [Retail]
)
WHERE
  ([Business Division].[Business Division].&[11])

What do I need to change to get the Measure using the correct query context with named sets and return the correct results?