How to filter MDX result Set by Current Year?

Below MDX query is to perform dynamic monthly comparison of a measure with current and previous year months,  to achieve this, a current year parameter is applied as like below

WITH
MEMBER [Measures].[ActiveLearnerPercentPreviousYear] as ([Measures].[Active Learner %],
PARALLELPERIOD ([MonthlyPeriod].[Calendar Period].[Month],12,[MonthlyPeriod].[Calendar Period].CurrentMember)),  FORMAT_STRING ='Percent'
MEMBER [Measures].[ActiveLearnerPercentCurrentYear]   as([Measures].[Active Learner %])
SELECT  {[Measures].[ActiveLearnerPercentCurrentYear] , [Measures].[ActiveLearnerPercentPreviousYear] } ON COLUMNS ,
([MonthlyPeriod].[Calendar Period].[Month], [MonthlyPeriod].[Month No].[Month No]) ON ROWS
FROM [MonthlyMetrics]
WHERE (
      strtomember("[MonthlyPeriod].[Year].&[" + Format(now(), "yyyy") + "]")
      )

MDX Named Set to Get Months of Current and Previous Years

MDX to Create Named Set to Get Months of Current and Previous Years

WITH

SET [Previous Year Months]

AS (StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ CSTR(CINT(FORMAT(NOW(), "yyyy"))-1) + '].&[1]'):StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ CSTR(CINT(FORMAT(NOW(), "yyyy"))-1) + '].&[12]'))

SET [Current Year Months]

AS (StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ FORMAT(NOW(), "yyyy") + '].&[1]'):StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ FORMAT(NOW(), "yyyy") + '].&[12]'))

SELECT [Previous Year Months] ON COLUMNS

FROM [CUBE]

MDX Named set to filter Dimension member based on their Text value

Named set to filter Dimension member based on their value or text:

FILTER([License].[Seat Class].ALLMEMBERS,
INSTR([License].[Seat Class].CURRENTMEMBER.MEMBER_CAPTION,'Free') >0 )


The above MDX gives the set of Seat Class containing name like '%Free%'

MDX Tuple to Convert Seconds to hh:mm:ss

vba!Format(vba!fix([Measures].[Avg Usage by Active Users] / 3600) ,"00") + ":" +  vba!Format((vba!fix([Measures].[Avg Usage by Active Users] / 60) - (vba!fix([Measures].[Avg Usage by Active Users] / 3600) * 60) ) ,"00") + ":" +  vba!Format([Measures].[Avg Usage by Active Users] - (vba!fix([Measures].[Avg Usage by Active Users] / 60) * 60) ,"00")

Example MDX Calculation for SSAS Cube

Example MDX Calculations

Sales Growth

([Time].CurrentMember, [Measures].[Sales]) - ([Time].CurrentMember.PrevMember, [Measures].[Sales])

PP Growth

([Time].CurrentMember, [Measures].[Sales]) - (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

YTD Sales

sum(ytd([Time].CurrentMember), [Measures].[Sales])

YTD Sales Using PeriodsToDate

sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

Product Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

Product Total Percentage

([Product].CurrentMember, [Measures].[Unit Sales]) / ([Product].[All Products], [Measures].[Unit Sales]) * 100

Average Stock - Brute Force

Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity]) / Count(Descendants([Time].CurrentMember, [Month]))

Average Stock - Elegant

Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Closing Period - Brute Force

(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

Closing Period - Elegant

(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

Max for Period

Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Min for Period

Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Moving Average

Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

Products Down

Count(Filter(Descendants([Product].CurrentMember, [Product Name])             , ([Time].CurrentMember, [Measures].[Unit Sales])               < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales]))) / Count(Descendants([Product].CurrentMember, [Product Name]))

Dynamic Custom Default Member

Tail(Filter([Time].[Month].Members      , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

Properties Query

Filter([Store].[Store Name].Members        , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

Products Down Query

select       {{{[Measures].[Products Down]}         * {[Customers].DefaultMember}         * {[Product].DefaultMember}}} on columns      , {[Time].&[1998].&[Q1].&[1]         , [Time].&[1998].&[Q1].&[2]         , [Time].&[1998].&[Q1].&[3]         , [Time].&[1998].&[Q2].&[4]         , [Time].&[1998].&[Q2].&[5]         , [Time].&[1998].&[Q2].&[6]         , [Time].&[1998].&[Q3].&[7]         , [Time].&[1998].&[Q3].&[8]         , [Time].&[1998].&[Q3].&[9]         , [Time].&[1998].&[Q4].&[10]         , [Time].&[1998].&[Q4].&[11]} on rows  from      [Sales_MDX2]

MDX Query

select     {[Measures].[Unit Sales]      , [Measures].[MA]} on columns     , {Descendants([Time].[Year].&[1997], [Month])} on rows from     [Sales_MDX2]

Convert Seconds to hours, minute, seconds (hh:mm:ss) in MDX

WITH MEMBER [Measures].[TimSpent (HH:MM:SS)] AS '[Measures].[Timespent]/ 86400', FORMAT_STRING = "hh:mm:ss"

SELECT [Assignment].[Element].[Element] ON ROWS, {[Measures].[Timespent], [Measures].[TimSpent (HH:MM:SS)]} ON COLUMNS

FROM [Activity]

To get Sales of Current Year, Previous Year, etc using Lag anf Lead functions

Current Year Sales:

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]"))}
on Columns
FROM [Sales]

Result

Previous year Sales

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lag(1))}on Columns
FROM [Sales]

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lead(-1))}
on Columns
FROM [Sales]

Result

Named Set for Months in Current Financial Year

ORDER(
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1]")
:
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN  CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE  CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)
,[Date].[Financial Period].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)

MDX named Set for Financial Year

Current Financial Year

STRTOMEMBER("[Date].[Financial Period].[Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]")

Last Financial Year

STRTOMEMBER("[Date].[Financial Period].[Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy") -1
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 2) END + "]")

Named Set for Current Month calculation

WITH SET [Calendar Months]
AS EXTRACT(STRTOMEMBER("[Date].[Month].&["+cstr(month(NOW()))+"]",CONSTRAINED)
*{[Date].[Calendar Period].[Month]},[Date].[Calendar Period])
SELECT NON Empty[Calendar Months]  on Rows, [Measures].[Actual Amount] on Columns FROM [Sales]

Named Sets for Time Dimensions

--Time examples

--How to define a default value for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")

--How to create a named set for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
{StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")}

--How to create a named set referring to a separate column in the table used for the time dimension as a member property
{Filter([Time].[Month].Members, [Time].CurrentMember.Properties("IsCurrentMonth") <> "0").Item(0).Item(0)}

--Referring to a named set called Current Month
[Current Month].Item(0).Item(0)

--Referring to last 6 months (up to current month)
LastPeriods(6, [Current Month].Item(0).Item(0))

--How to use the named set Current Month for positioning on the quarter level
LastPeriods(6, Ancestor([Current Month].Item(0).Item(0), Time.Quarter))

--Last 6 members on month
{Tail([Time].[Month].members,6)}

--Last 6 members on month where actual is larger then 0
{Tail(Filter({[Time].[Month].members},[Measures].[Actual] > 0),6)}

--All months where actual is larger then 0
{Filter({[Time].[Month].members},[Measures].[Actual] >0)}

--Order months depending on actual
{Order([Time].[Month].members, [Measures].[Actual], BDESC)}

MDX Named Set using Filter and Except functions

--Return members filtered on a custom property. We need to exclude the All level from the set to filter on since it does not have any custom properties.

Filter(Except([Warehouse].Members,{ [Warehouse].[All Warehouse]}), [Warehouse].CurrentMember.Properties("PROPERTYNAME") = "PROPERTYVALUE")


--How to create a named set referring to a separate column in the table used for the time dimension as a member property

{Filter([Time].[Month].Members, [Time].CurrentMember.Properties("IsCurrentMonth") <> "0").Item(0).Item(0)}

--To get product names with sales amount greater than 0
([Product].[Product Name].[Product Name] ,NOT ISEMPTY([Measures].[Sales Amt]OR (NOT ISEMPTY([Measures].[Sales Amt] > 0))
FILTER
(
) )
)

MDX query for Top and Bottom Count

Top 10 Campaigns based on Opportunity Count

WITH SET [TopOpportunity] AS {TopCount([Opportunity].[Campaign Name].[Campaign Name], 10, [Measures].[Opportunity Count])}
SELECT [TopOpportunity] ON ROws,
[Measures].[Opportunity Count] ON Columns
FROM [PreSales]

Bottom10 Campaigns based on Opportunity Count

WITH SET [BottomOpportunity] AS {BottomCount([Opportunity].[Campaign Name].[Campaign Name], 10, [Measures].[Opportunity Count])}
SELECT BottomOpportunity ON ROws,
[Measures].[Opportunity Count] ON Columns
FROM [PreSales]

MDX Named Set to find the ratio of Dimension Members

In order to find out ratio of Non-Billable hrs of Various cost type use below named set:

[Measures].[Non-Billable Hrs]/(Root([Tasks]),[Measures].[Non-Billable Hrs])*100

where Tasks is the dimension name.

 


















Similary in order to find Sales Proportion of different country use the below named set

where Locationis the Dimension name.

[Measures].[Sales Amt]/(Root([Location]),[Measures].[Sales Amt])*100

Named Set to Display Months of Previous Year

MDX Named set to get Months of Current Year

Order by Descending Order

Non Empty

Create and use Named Set in MDX Query

How to use named set in MDX Query:

MDX Named set to get Last 3 Quarters

How to get last 3 Quarters from hierarchy with data structure "
[Date].[Calendar Year].[Year].&[2011].&[Quarter - 1]"

MDX Named Set to Get Last 3 Months

How to get last 3 months from hierarchy with structure "
[Date].[Calendar Year].[Year].&[2007].&[Quarter - 3].&[2007]&[8]"

MDX Named set to get Last 3 Years

How to get last three years from hierarchy "" with structure " [Date].[Calendar Year].[Year].&[2007]"
[Date].[Calendar Year].[Year]