How do I write an MDX statement to do something differently if the current shown period is incomplete? <p>Using MDX, I need to show a "Closing" stock amount which is basically the last value for the period being shown.</p> <p>For example, if they are looking at the data by week, then the closing stock is the last day of that week, month is the last day of that month etc.</p> <p>I am using the following to show me this: <code>(ClosingPeriod([Date].[Retail].[Date], [Date].[Retail].CurrentMember), [Measures].[On Hand Quantity])</code></p> <p>The problem I am having is when they are looking at a period that is not yet finished. For example, if they are looking at current week, we want to show the most recent value that is not 0. If they are looking at current month or even current year, it should still show yesterday as that period level has not yet finished.</p> <p>How do I say "if we are looking at the current period, show yesterday, otherwise use my code above to show closingperiod"?</p> <p>I can use <code>StrToMember('[Date].[Calendar].[Date].&amp;[' + Format(Now(), 'yyyy-MM-dd') + 'T00:00:00]')</code> to get the current dates member.</p> <p>I was doing the below with the assumption that if I don't have a value, then it must be incomplete, however future periods are showing a value which is not what I want:</p> <pre><code> CoalesceEmpty( (ClosingPeriod([Date].[Retail].[Date], [Date].[Retail].CurrentMember), [Measures].[On Hand Quantity]), (StrToMember('[Date].[Calendar].[Date].&amp;[' + Format(Now(), 'yyyy-MM-dd') + 'T00:00:00]'), [Measures].[On Hand Quantity]) ) </code></pre>