We are using a bar chart for showing projects date range to provide a “gantt chart”. The default gantt chart is intentionally not used here.
We now need a filter that shows the years in which projects are active as a select list. A project is considered active for a year if the start and end dates are in that year, or if the year is within the start and end dates.
For example, there is project A with a start date of 01.03.2022 and an end date of 01.08.2024. Then, in 2022, 2023, and 2024 the project is considered active and will be displayed upon selecting those years.
Is it possible to use this filter as Pages in the bar chart based on start and end date? Ideally, the years should automatically appear in the dropdown. Below you’ll find a screenshot of the bar chart and the ideal filter view. This is just edited and not a real filter.
Thank you for the answer. You can use the Open Issues measure as a starter. Please change the “Created at” and “Resolved at” with your dates.
Then the formula should look something like this:
CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[Issues created]
- [Measures].[Issues resolved]
))
+ [Measures].[Issues created]
- [Measures].[Issues resolved]
)
ELSE
-- optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Custom Created at'),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get('Custom Resolved at'),
[Time].CurrentHierarchyMember),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END