Hi,
I created a DateDiffDays measure that make difference between two dates.
DateDiffDays
(
[Measures].[End Date],[Measures].[Issue Target end]
)
the problem is that i can see the output in column for single issue, but when I reuce the column, I can not see the total of column values.
Also if i add TOTAL of ROWS, i can not see it.
I can only use a cumulative sum calculation for see the total of the column values.
Sorry if my question is incomplete, it is my first post in the community.
Hi @tulliodisimone,
Welcome to the eazyBI community. Your question and the provided details are spot on! Sorry, it took a while to give you an answer.
In the detailed “Issue” level, eazyBI can retrieve both dates. Thus it can return the result you require. However, in the “Project”, “All” levels, and the total, the dates are empty, and eazyBI can’t determine a result.
To overcome that, I recommend splitting the calculation into two parts. The first part determines the result in the “Issue” level. The second part will take care of the upper levels. You can do that with the CASE statement. See the first attempt at the calculation below:
CASE WHEN
-- determine Issue dimension level
[Issue].CurrentHierarchyMember.Level.Name = 'Issue'
THEN
DateDiffDays(
[Measures].[End Date],
[Measures].[Issue Target end]
)
END
Now, to get the results in levels other than “Issue”, the calculation must iterate through these issues, calculate the values, and sum them in the ELSE branch. eazyBI can do that with the help of the Descendants() function. To improve its performance, I recommend using the Filter() function to reduce the number of considered issues. See the whole calculation below with comments:
CASE WHEN
-- determine Issue dimension level
[Issue].CurrentHierarchyMember.Level.Name = 'Issue'
THEN
DateDiffDays(
[Measures].[End Date],
[Measures].[Issue Target end]
)
ELSE
Sum( -- sum the total. Use Avg() to calculate the average
Filter(
-- iterate through issues and filter to retrieve relevant issues
Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- filter conditions
Not IsEmpty([Measures].[End Date])
AND
Not IsEmpty([Measures].[Issue Target end])
),
-- numeric expression to evaluate the issues
CASE WHEN [Measures].[Issues created] > 0
THEN
DateDiffDays(
[Measures].[End Date],
[Measures].[Issue Target end]
)
END
)
END
See an example with different dates below:
Suppose you add the Time dimension to the report, for example, the pages, and select a specific period. In that case, the report will retrieve the issues created in the selected period. Let me know if that is a viable scenario and if you would want to change that.
Please look at the eazyBI documentation page for more information on defining calculated measures -Calculated measures and members.
Best,
Roberts // support@eazybi.com
Hi Roberts,
thanks for your support, I’ll analyze your solution.
In the mean time, I found a solution, analyzing others working measures, and it works:
NonZero(
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
[Measures].[Transitions to status],
[Transition Status].[Completed]
)>0
),
DateDiffDays(
[Measures].[End Date],
[Measures].[Issue Target end]
)
)
)
)