Filters for Time Periods

Questions & Answers
1

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’)
))

2

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}
)

31
Screen Shot 2018-06-27 at 12.02.31.png811×646 56.2 KB

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

1 Like
3

Thanks for helping me out here :smile:

4

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

5

Hello Martins;

in my case, I want to have in the month, dates between today and today +30. To display the Tempo planned hours over the period in question.

Thank you for what you do for the community

6

@Nanangoran

Try this formula:

    Aggregate(Filter(
[Time].[Day].Members,
DateBetween([Time].CurrentMember.StartDate,
'today','30 days from now')
))

Martins / eazyBI

1 Like
7

good
Thank you very much