How to retrieve the PrevMember of a dynamic Time element

Questions & Answers
1

I have a report where I sum the issues of type Defect based on the Selected Pages elements.
The purpose of this report is to provide a dynamic view on the total number of issues of type Defect for a selected period.

So if user selects Current Month, he will see the current Month number and previous Month number

Getting the current month is working well with following

Sum(
 
    CrossJoin(
      {[Issue Type].[Defect]},
      {[Time].CurrentHierarchyMember}
  )
 )

When trying to reuse the same formula to get the number of the PrevMember I received an error stating there are no members.

Sum(
 
    CrossJoin(
      {[Issue Type].[Defect]},
      {[Time].CurrentHierarchyMember.PrevMember}
  )
 )

How can I therefore get the total number of issue type defects for the same period -1?

2

Hi @Fabrizio,

The tuples always take into account the report context.
Therefore, the [Time].CurrentHierarchyMember is always what has been selected in the report pages.

When you use the Sum without any defined measure, the default measure “Issues created” is applied automatically.

Therefore, your expression is actually perceived in the following way.

Sum(
    CrossJoin(
      {[Issue Type].[Defect]},
      {[Time].CurrentHierarchyMember}
  )
 )

First, the default measure is added.

Sum(
--crossjoin creates the set  
   CrossJoin(
      {[Issue Type].[Defect]},
      {[Time].CurrentHierarchyMember}
  ),
--the default measure is summed over the set
 [Measures].[Issues created]
 )

The next step is cross-joining the Issue Type and Current Time - that is actually a tuple of a single member from each dimension.

So the applicable expression would be as follows.

([Issue Type].[Defect],
 [Time].CurrentHierarchyMember,
 [Measures].[Issues created])

Since the [Time].CurrentHierarechyMember is taken from the report context, you might drop that out. Then you are left with the following simple tuple.

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

The best about this expression is that it does not override the Time dimension, and you can add the standard calculation to it.
Please read more about the standard calculations here - standard calculations.

The standard calculation, in this case, might be Time ago->Previous period.

Regards,
Oskars / support@eazyBI.com

3

Thank you.

For info I used this solution which also does the job:

Sum(
 Tail(PreviousPeriods([Time].CurrentHierarchyMember),1),
 [Measures].[Defects created]
  )
 )
4

Hi @Fabrizio,

Your expression might generally work for the majority of Time dimension members. However, that could fail for the first members of a certain hierarchy level because PreviousPeriods returns higher hierarchy levels where that is efficient.

The PreviousPeriods function for date of 01 Jan 2023 returns members at the Year level of previous years.
The PreviousPeriods function for month of April 2023 would return Q1 of 2023 and the previous years.

The approach with standard calculations already handles these situations and finds the proper member at the proper hierarchy level.

Regards,
Oskars / support@eazyBI.com