Calculated member on time dimension with conditions

Questions & Answers
1

Greetings,
I have a calculated member for my time dimension that gives me all the date members between the last day of last month and 6 months before that date:

Aggregate(
  [Time].[Month].DateMembersBetween('7 months ago', 'one day before first day of current month')
)

Now, I would like to have this for the first two weeks of the month, and to include current month if the date of today is > 15th, so what I did is:

`CASE
WHEN 
DateDiffDays('first day of current month', now()) > 15
THEN 
Aggregate(
  [Time].[Month].DateMembersBetween('6 months ago', 'today')
)
ELSE
Aggregate(
  [Time].[Month].DateMembersBetween('7 months ago', 'one day before first day of current month')
)
END
`

but it doesn’t seem to go anywhere, I don’t get any time member when I try to use this calculated member.
Any idea? I tried to build a measure with the check I made in the first when clause, and it seems to work just fine.

2

Hi @Mauro_Bennici ,

While there is little difference in the construction sequence for calculated measures, the construction sequence does make a difference for the calculated member.
To keep the dimension member aggregated, you should raise the Aggregate function to the top and bring the conditional selection to the lowest level.
The same words placed in a different order provide the expected result.

Aggregate(
  [Time].[Month].DateMembersBetween(
--expression for start date
  CASE WHEN DateDiffDays('first day of current month', now()) > 15 
   THEN
  '6 months ago' 
   ELSE
  '7 months ago' 
  END,
--expression for end date
  CASE WHEN DateDiffDays('first day of current month', now()) > 15 
   THEN
  'today' 
   ELSE
  'one day before first day of current month'
  END)
)

Regards,
Oskars / support@eazybi.com

1 Like
3

thanks very much @oskars.laganovskis!
What’s the cause of this behaviour?
regards

4

While the aggregation within a measure passes on a single numeric value, the calculated members within dimensions are aggregated sets of members. Therefore - the calculated member should have the Aggregation on the top.