MDX examples


Please find calculated measure examples from advanced training below:

Calculated members in "Measures" dimension

Issue change

[Measures].[Issues created] - [Measures].[Issues resolved]

Issues created in time period from total, %

[Measures].[Issues created] 
/
( [Measures].[Issues created],
  [Time].CurrentHierarchy.DefaultMember )

Issues created from parent, %

CASE WHEN [Time].CurrentHierarchyMember IS
  [Time].CurrentHierarchy.DefaultMember
THEN 1
ELSE
  [Measures].[Issues created] /
  ( [Measures].[Issues created],
    [Time].CurrentHierarchyMember.Parent )
END

Bugs created

( [Measures].[Issues created], [Issue Type].[Bug] )

Bugs from All issue types, %

[Measures].[Bugs created] /
[Measures].[Issues created]

Issues created in previous period

( [Measures].[Issues created],
  [Time].CurrentHierarchyMember.PrevMember )

Issues created change, %

CASE WHEN [Measures].[Issues created in previous period] > 0
THEN
  ( [Measures].[Issues created] - 
    [Measures].[Issues created in previous period] ) /
  [Measures].[Issues created in previous period]
END

Cumulative issues created

Sum(
  {
    PreviousPeriods([Time].CurrentHierarchyMember),
    [Time].CurrentHierarchyMember
  },
  [Measures].[Issues created]
)

Cumulative issues resolved

CASE WHEN [Measures].[Issues resolved] > 0 THEN
  Sum({PreviousPeriods([Time].CurrentHierarchyMember),
    [Time].CurrentHierarchyMember},
    [Measures].[Issues resolved]
  )
END

Cumulative issues resolved trend

LinRegPoint(
  -- output x
  DateToTimestamp([Time].CurrentHierarchyMember.StartDate),
  -- input period
  Filter(
    [Time].CurrentHierarchyMember.Level.Members,
    DateBetween([Time].CurrentHierarchyMember.StartDate,
      '3 months ago','today')
  ),
  -- input y
  [Measures].[Cumulative issues resolved],
  -- input x
  DateToTimestamp([Time].CurrentHierarchyMember.StartDate)
)
 


Issues created average last 3 months

Avg(
  LastPeriods(3, [Time].CurrentHierarchyMember),
  [Measures].[Issues created]
)
)

Logged hours for two issue types:

Aggregate(
  {
    [Issue Type].[Bug],
    [Issue Type].[Story]
  },
  [Measures].[Hours spent]
)

Calculated members in Time dimension

Current month

Aggregate({
  [Time].[Month].CurrentDateMember
})

Last month

Aggregate({
  [Time].[Month].CurrentDateMember.PrevMember
})


Last 3 months

Aggregate(Filter(
  [Time].[Month].Members,
  DateBetween([Time].CurrentMember.StartDate,
    '3 months ago','today')
))

Current week

Aggregate({[Time.Weekly].[Week].CurrentDateMember})

Today

Aggregate([Time].[Day].DateMember('today'))

Yesterday

Aggregate([Time].[Day].DateMember('yesterday'))

Calculated members in Issue dimension

Issues with prefix SMT

Aggregate(
  Filter(
    [Issue].[Issue].Members,
    [Issue].CurrentHierarchyMember.Name MATCHES ".*SMT.*"
  )
)

Calculated members in Issue Type dimension

Bugs & Stories

Aggregate({
  [Issue Type].[Bug],
  [Issue Type].[Story]
})



Calculated member in Sprints dimension


Last 3 sprints by sprint start date and other sprints (with empty sprint start date)

Aggregate({
  Union(
  Tail(Order(
    Filter([Sprint].[Sprint].Members,
      NOT isEmpty([Measures].[Sprint Start Date])),
    [Sprint].CurrentMember.get('Start date'), BASC
  ), 3)
  ,
  Filter([Sprint].[Sprint].Members,
      isEmpty([Measures].[Sprint Start Date])
      AND
      [Sprint].CurrentMember.Name <> "(no sprint)")
  )
})




Calculated member in Measures dimension for Last 6 sprints from the report context (other dimensions used in the report) 


Last 6 sprints for the report context.


Case when
[Sprint].Currentmember.Level.Name = 'Sprint'
Then
Case when [Sprint].CurrentMember.Name <> '(no sprint)'
Then
NonZero( Rank(
[Sprint].CurrentMember,
Tail( Order(Filter(
[Sprint].[Sprint].Members,
[Measures].[Issues created] >0), 
[Sprint].Currentmember.Get('Start date'), BASC)
,6)))
End
Else
[Measures].[Issues created] 
End