Replace date with custom string

Questions & Answers
1

I want to rename the time period with sprint name for a report, tried to create below calculated member but its not working. Any help would be appreciated

Aggregate(
Generate(
Filter(
[Time.2 Weeks].[2 Weeks].Members,
DateBetween([Time].[Year].CurrentMember.StartDate,‘2021-01-01’,‘2022-09-30’)
)
,StrToMember(‘PGOAL Sprint’), [Time].[Year].Name
)
)

2

You can define a new calculated member in Time dimension with a specific name in the member. Here I used a bit updated formula and created a new calculated member in Time 2. Weeks hierarchy with the name PGOAL Sprint

image
image1596×886 95.9 KB

Then I can select this calculated member in the report:

image
image1188×412 31.2 KB

As mentioned above. I used a slightly modified formula for this.

Aggregate(
[Time.2 Weeks].[2 Weeks].DateMembersBetween(
  "2021-01-01","2022-09-30")
)

We suggest using the function DateMembersBetween to filter the needed periods. It is easier to use and it will work faster and be more reliable.

While it is not needed in the new suggested formula, here are some points on how to address the year level if needed in formulas. You would like to use one/the same hierarchy with functions Aggregate and Generate. [Time.2 Weeks].[2 Weeks] and [Time].[Year] represent level Year in two different hierarchies. They will not work well/as expected within one formula with functions Generate and Aggregate. If you would like to address the Year level of the 2 Weeks hierarchy, please use this [Time.2 Weeks].[Year]. Or you can use this formula to address Year level in any hierarchy: [Time].CurrentHierarchy.Levels("Year")

Daina / support@eazybi.com

3

Thanks Daina for your inputs. your point is valid but I am looking to replace the dates shown on X-axis with custom string(Sprint name) but I can not use Sprint dimension here as these issues are not linked with the sprint. We are having some complex structure. Seeking your support for this.