Filters for Time Periods

Hi

I want to create some calculated members based on the following time periods

  • 9 to 12 months ago
  • 6 to 9 months ago
  • 3 to 6 months ago
  • 3 months ago to now

I’m trying do do this as follows - but it’s not returning any members :frowning: - can somebody please help?

Aggregate(Filter(
[Time].[Month].Members,
DateBetween([Time].CurrentMember.StartDate,
‘9 months ago’,‘12 months ago’)
))

The only one I can get to work is this one…

Aggregate(Filter(
[Time].[Month].Members,
DateBetween([Time].CurrentMember.StartDate,
‘3 months ago’,‘now’)
))

Hi Graeme,

You can use .CurrentDateMember for that. It is different from .CurrentMember that it returns the member for the current time in real life. For example, what day, month or year today is. But remember to specify what level of the time member you want to return.
Lag() is just a shortcut for writing .PrevMember multiple times.
And not need for filter() :slight_smile:

Aggregate(
  {[Time].[Month].CurrentDatemember.Lag(11):
  [Time].[Month].CurrentDatemember.Lag(9)}
)

Aggregate(
  {[Time].[Month].CurrentDatemember.Lag(8):
  [Time].[Month].CurrentDatemember.Lag(6)}
)

Aggregate(
  {[Time].[Month].CurrentDatemember.Lag(5):
  [Time].[Month].CurrentDatemember.Lag(3)}
)

Aggregate(
  {[Time].[Month].CurrentDatemember.Lag(2):
  [Time].[Month].CurrentDatemember}
)

Gvido Neilands,
gvido@flex.bi,
eazyBI service partner

1 Like

Thanks for helping me out here :smile:

Hi,

I believe your first attempt also would work if you have the opposite sequence of members:

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

@Gvido, I appreciate your help on this!

Martins / eazyBI support