MDX Help - Returning month as a value

Question

I am trying to use MDX to replicate an Excel formula in SharePoint. The hangup I am having is in Excel, I can reference the selected month as a number and use that number in my formula (i.e. June is 6, October is 10, etc.).

Is there a way I can have MDX decipher a month chosen in a filter as a number like I do in Excel?

If that's possible, is there also a way to use that to calculate the number of months left in the year (If I choose September in the filter, a formula to know that Sept = 9, 12 - 9 = 3 months left in the year)?

Thank you very much for your help! Michael

Answer 1

You can use VBA type functions to extract the month number from a date:

e.g.

WITH  MEMBER [Measures].[Full Date] as 'NOW()'
-- The Second Calculated Member is the Day part of the first calculated member.
MEMBER [Measures].[What Day] as 'DAY([Full Date])'
-- The Third Calculated Member is the Month part of the first calculated member.
MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
-- The Fourth Calculated Member is the Year part of the first calculated member.
Member [Measures].[What Year] as 'YEAR([Full Date])'
SELECT
   {[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS
FROM Sales

See this reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1555eb27-f277-4f60-87ca-a2c2d6c12917/equivalent-function-in-mdx-for-monthx-as-in-sql?forum=sqlanalysisservices