Filter issues by their date-time customfields

Hello EazyBI Team!

We have two date-time customfields named respectively: “Unavailability to” and “Unavailability from” and we are looking to create a report, that would sum up total time beetween this two fields, per component for a selected month. We are using Component member as a row and Month hierarchy member as a page.

As a part of this goal, we want to include every issue that have value for “Unavailability from” before selected month, and value of “Unavailability to” after selected month and we have great problem with that.

My formula looks like this:

DateInPeriod([Issue].CurrentHierarchyMember.get(‘Unavailability to’), [Time].CurrentHierarchy.CurrentMember.NextMember)
DateInPeriod([Issue].CurrentHierarchyMember.get(‘Unavailibility from’), [Time].CurrentHierarchy.CurrentMember.PrevMember)
(DateDiffMinutes([Measures].[Issue Unavailibility from],
[Measures].[Issue Unavailability to]))

As result my table looks like this (I have to hide every name of our Components, sorry for that):

The sum is correct, but the same value is displayed for every component, even if we have only two issues that fulfil our filter conditions, and have only one component selected per issue. Because not every of this issues have been created during selected month I cannot add line like this [Measures].[Issues created] >0 to my formula. The same problem with the lines like [Measures].[Issues with unavailability to] > 0 or
[Measures].[Issues with unavailibility from] > 0 , they just doesn’t work in my filter.

Do you have any recomendaiton to resolve our problem?

Best regard
M. Sz

Hi @M.Sz

Welcome to eazyBI community!

You are almost there: yes, the reason why the same value appears in all rows is because of the missing measure in the formula.
You may want to add as a filter condition the same standard measure [Issues created] >0, only in a tuple together with the default Time dimension member to take into account the report context (selection in rows, pages, and columns), except Time dimension.
The filter condition would be the following:

([Measures].[Issues created],

Add it as the last filter condition in the formula.

Ilze /