How to create an MDX formula to filter weeks in 2-week intervals?

Questions & Answers
, ,
1

Hello, everyone!

I’m trying to create an MDX formula in my report to filter weeks every two weeks within a range of 6 months ago to today. I want to use the DateMembersBetween function to select all the weeks within this range and then apply a filter to ensure that only weeks in 2-week intervals are returned.

My current formula looks like this:

Aggregate(
[Time.Weekly].[Week].DateMembersBetween(‘6 months ago’, ‘today’)
)

How can I adjust this formula to return weeks that occur every two weeks?

I’ve tried applying some filters, but I haven’t been able to properly set the condition to capture the 2-week intervals. Does anyone have a solution or could suggest a way to adjust this formula?

Thanks in advance for your help!

2

Hi @Diana_Osorio,

Please clarify if you want to return the two-week intervals or every second week in the specific interval.

If you need the two-week intervals, you might build a custom hierarchy within the Time dimension.
Please read more about that here - Custom time hierarchies.

After that, you can create a calculated member within this new hierarchy.

last 6months by 2week periods
last 6months by 2week periods986×454 29.9 KB

Please make sure you are using the proper hierarchy references.

The new expression would be very similar to the existing one.

Aggregate(
  [Time.2 Weeks].[2 Weeks].DateMembersBetween(
    "6 months ago",
    "today")
  )

However, if you need to take every second week of the last 6 monh period, you might add an extra filter on your initial expression to only take every second week.
The expression might then be as follows.

Aggregate(
 Filter(
--weeks of last 6 months 
   [Time.Weekly].[Week].DateMembersBetween(
     "6 months ago",
     "today"),
--filter condition - odd weeks
 (Round([Time.Weekly].CurrentHierarchyMember.Key/2,0)
  -
 [Time.Weekly].CurrentHierarchyMember.Key/2)>0
))

Regards,
Oskars /support@eazyBI.com