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:
Sum(Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod([Issue].CurrentHierarchyMember.get(‘Unavailability to’), [Time].CurrentHierarchy.CurrentMember.NextMember)
AND
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