Epics Date Filter

Hello,

i work on a Project where i need to define Filter for the Epics we have in our Project.

I need to find “Epics are Over Due and still open” and “Epics Due in two weeks and still open”

I tried to rework the demoboard: https://eazybi.com/accounts/1000/cubes/Issues/reports/249876-issues-due-and-overdue

but how can i add the Two weeks exception to the Measures and how can i add the still open Part?

i read in this documentation: https://docs.eazybi.com/eazybi/analyze-and-visualize/date-filters

but for me it is hard to define the code that fits for this filter.

is there any documentation where i can find some code snippets or any demo where i can have an example how the Measure should work

i added my measures and my time and issue configuration. I thought i could add in the time page a filter named " next two weeks" or something.

Thanks for your help

have a nice day!

Hi @KatrinIppisch,
Welcome to the eazyBI community! :wave:

It is great to see that you already have found the report in our demo account that calculates Overdue issues.

For your reports you can use measure Issues due as it already includes Open or unresolved issues.
From eazyBI documentation: Issues due are due (or unresolved) issues that do not have resolution and resolution date. On the Time dimension they are grouped by issue due dates (if issue does not have a due date then it will be counted only in All Times member).

Now about your measures:

First - “Epics are Over Due and still open”. The formula you can use is straight from demo report “Overdue Issues”:

CASE WHEN
 [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
-- when no time dimension is used in the report get today
  SUM(
PreviousPeriods(
  [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),
[Measures].[Issues due]
  )
WHEN
-- with time dimension in the report apply the calcualtion for past dates and today only
  DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)
  OR
  DateInPeriod("Today", [Time].CurrentHierarchyMember)
THEN 
  SUM(
PreviousPeriods(
  [Time].CurrentHierarchyMember),
[Measures].[Issues due]
  )
END

For your second measure that check issue dues in the next two weeks you can create a measure in Measure dimension:

Sum(
  Filter(Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
 DateBetween([Measures].[Issue due date],
 'today',
 '2 weeks from now'
 )),
 [Measures].[Issues due]
 )

Or use Issue due measure together with a calculated member in Time dimension:

Aggregate(
  [Time.Weekly].[Week].DateMembersBetween('today', '2 weeks from now')
)

You need to create this member in Weekly hierarchy:

Best,
Gerda // support@eazyBI.com