MDX choose previous quarter if selected one is not complete

Question

I am creating a parameter list in SSRS using MDX, and so I just want to list the completed quarters. The way it works is we have "records" that get submitted on certain dates. If the last date that it gets submitted is not equal to the quarter end date then restrict the parameter list to be up to the last quarter.

So The last submission was on Oct which is Quarter 3, but since October is not the end of the quarter, we want it to show the list upto quarter 2. Here is my MDX for the drop down list

WITH 
  MEMBER [Measures].[ParameterCaption] AS 
    [Date - Submitted].[Submitted   Year-Quarter].CurrentMember.Member_Caption 
  MEMBER [Measures].[ParameterValue] AS 
    [Date - Discharge].[Submitted Year-Quarter].CurrentMember.UniqueName 
  MEMBER [Measures].[ParameterLevel] AS 
    [Date - Submitted].[Submitted Year-Quarter].CurrentMember.Level.Ordinal 
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS
 ,NonEmpty
  (
    [Date - Submitted].[Submitted Year-Quarter].Children
   ,[Measures].[Records Count]
  ) ON ROWS
FROM [Records Cube]
WHERE 
  [Records].[Submission Status].&[Submitted];

any ideas on what I can add to this so that it can check whether it is the end of the quarter, and if not, just included [Submitted Year-Quarter of the one before this

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-02-03T13:42:29

You have create two measures and check if both is non empty:

    WITH 

 Member [Measures].[MonthID] as
    [Date - Submitted].[Submitted Year-Month].CurrentMember.Properties('Key')

 Member [Measures].[MonthNumber] as
    Right([Date - Submitted].[Submitted Year-Month].CurrentMember.Properties('Key'),2)

 Member [Measures].[IsLastMonth] as
    IIF(
        [Measures].[MonthNumber] / 3 - cint([Measures].[MonthNumber] / 3) = 0
        and [Measures].[Records Count],
        1,
        Null
    )    

SELECT 
  {
    [Measures].[MonthID]
   ,[Measures].[MonthNumber]
   ,[Measures].[IsLastMonth]
  } ON COLUMNS
 ,{
    NULL:
    Order(
        NonEmpty(
            [Date - Submitted].[Submitted Year-Month].[Month].Members,
            [Measures].[IsLastMonth]
        ),
    [Measures].[MonthID],
    BDESC
    ).Item(0)
 }  ON ROWS
FROM [Records Cube]
WHERE 
  [Records].[Submission Status].&[Submitted];

MDX choose previous quarter if selected one is not complete

1 Answers