I am creating a closed issue report where I want to report the hours for items completed in the month where the issue is closed, not when the hours were expended. I am using Time and Issue for rows and measures for column
For measure, I have [Issues Closed] and [Hours Spent] , however, the Hours Spent reports the hours in the month they occurred. How would I get this to reflect the hours in the month the issue closed? Is there a total hours available in the issue dimension that could be leveraged and how?
You could try to display all the hours spent on the issues by filtering the report to display only issues that are closed and creating a new calculated measure for all hours spent.
To filter out only issues that are closed, click on the “Issues closed” column header and in the drop-down menu select “filter rows” and create a filter for the measure to be greater than zero. Please have a look at the picture:
If it is no longer necessary, you can remove the “Issues closed” column; the filter will keep it in the report.
Then create a new calculated measure that consists of a tuple from the measure “Hours spent” and the default member “All time” of the Time dimension. Please have a look at the formula below:
([Time].DefaultMember,[Measures].[Hours spent])
The new measure will display the entire hours spent on each issue. You can see that in the picture posted above. For example, the issue D1-6 had 3 hours logged in April 2017, but 7 hours entirely.
The filter does hide the column, but does not really change anything beyond the display.
The tuple works as you have shown. However, it gives me a total of all time spent on all issues when I try to manipulate it into a column. I’ll try to work that tuple to create a new measure.
The filter is necessary for the report to display only the closed issues. With the calculated measure or “Hours spent” the report would also display issues that are not closed.
What do you mean by “try to manipulate it into a column”?
I am trying to replace the hours spent column you show with the hours spent on items closed that month. so I would have rows for month and columns for issues closed and the total hours associated with those closed issues.
While creating the calculated measure I didn’t take into account that you would want to use it in the “All issues” or “Project” level of the Issue dimension. Try to add a condition that looks checks the current Issue dimension level and performs the according calculation. Please have a look at the code below:
CASE WHEN [Issue].CurrentMember.Level.Name = "Issue"
THEN val(([Time].DefaultMember,[Measures].[Hours spent]))
ELSE
-- if the current Issue dimension level is not Issue, then sum the values
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Hours spent] > 0
AND
[Measures].[Issues closed] > 0),
([Time].DefaultMember, [Measures].[Hours spent])
)
END
