StartDate doesn't work on custom 3 weeks hierarchies

Questions & Answers
1

I have created a 3 weeks custom hierarchy and would like to limit it to 2020. So I tried to use the following caculated member formula

Aggregate(Filter(
[Time.3 Weeks].[3 Weeks].Members,
DateBetween([Time.3 Weeks].CurrentHierarchyMember.StartDate, '2020-01-01', 
'2021-01-01')
))

However, the result is empty. What is wrong with the input?

2

Hi @rainerz64,

The formula for the calculated time member is valid.

  • Make sure that a calculated member is defined in the “3 Weeks” hierarchy (see picture below).
    image
    image1256×704 81.3 KB
  • Check at which date the first 3 Weeks cycle of the year starts.

Another option, you may use function CurrentDateMember to look up the “3 Weeks” of the current year, instead of hardcoding the dates for the year:

Aggregate({
  [Time.3 Weeks].[Year].CurrentDateMember.Children
})

Best,
Zane / support@eazyBI.com