Creating a customised time range - any days after the last Sunday of a month are counted in the following month

Questions & Answers
1

Our company has some processes that align month end dates with the last Sunday of a month. How can I create a customised time range that I can use instead of the normal calendar months?

The rule is

  • Any days after the last Sunday of a month are counted in the following month

Can the weeks of a year are to be put into a newly defined hierarchy of MyMonths (e.g. MyJan, MyFeb, MyMar, …) by calculating whether there are 4 or 5 weeks in a each of MyMonth members?
Is there a simple way to achieve this?

I have watched Roberts presentation It’s About Time from eazyBI Community Days 2019 - https://docs.eazybi.com/eazybijira/learn-more/training-videos/training-videos-on-specific-topics#Trainingvideosonspecifictopics-It’sAboutTime(30min)

I think it is some combination of elements from a formula for a calculated member Current week:

Aggregate(
  {
  [Time].[Day].DateMember(DateAdd('d',(WeekDay(Now(),2)-1)*-1,Now())):
  [Time].[Day].DateMember(DateAdd('d',7-WeekDay(Now(),2),Now()))
  }
)

and a formula for a calculated member similar to the last 30 days:

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

Hi @gmartel

Welcome to eazyBI community.

There are very limited options to create custom hierarchies in “Time” dimension in eazyBI.
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-AddcustomhierarchiestoTimedimension
You could explore the hierarchy with 4-week groups.

If that doesn’t help, it is somewhat likely that your described scenario would require a custom hierarchy which eazyBI can’t offer.
Usually, we do not recommend mixing months with weeks in the same hierarchy as it can lead to unexpected behavior when the week is split into multiple months.
We have some examples in our Demo account with calculated members to aggregate last N months in weeks

For example this one “Last 4 months by weeks”

Aggregate(
  [Time.Weekly].[Week].DateMembersBetween('4 months ago', 'today')
)

Perhaps you could also check the possibilities to create multiple calculated members to split the year in smaller but custom periods.

Martins / eazyBI support