Hello,
I haven’t found any similar topic in community. I would like to prepare table report with latest data values of sprint issues on specific date (to choose from filters). I would like to know:
a) Remainig Time
b) Original Time
c) Priority
d) Resolution
values in the past. Is it possible? Do you have any idea?
Hi @Bartosz ,
Welcome to the eazyBI community!
One way to approach this is by adding the Sprint dimension to the report pages and selecting the desired Sprint. Next, you can add the Time dimension “Day” levels to the rows. To see only relevant days - on which the Sprint was active, add the measure “Time within sprint” to the report. Filter the report rows by this measure. After that, you can remove it from the report.
Next, add the measure “Remaining estimated hours history” to the report to reflect your first requirement. Unfortunately, eazyBI doesn’t track the “Original estimate” history. Thus I recommend you display its value disregarding the Time dimension. Otherwise, the value will be tied to the respective issue creation date. See the suggested formula below:
([Measures].[Original estimated hours],
[Time].CurrentHierarchy.DefaultMember)
To see the issue Priority and Resolution changes, create two new calculated measures. Both have the same structure, the addressed dimension is subject to change. See the example for “Historic Priority” below:
Generate(
Filter(
Descendants([Priority].CurrentMember,[Priority].[Priority]),
[Measures].[Issues history] > 0
),[Priority].CurrentMember.Name, ", "
)
The report then could look similar to the one below:
In the screenshot you can see the issue OD-16 had a Priority change on November 24 and Resolution change on November 26.
Visit the eazyBI documentation page for more details on defining calculated measures - Calculated measures and members.
Best,
Roberts // support@eazybi.com
Hi @roberts.cacus
thanks for reply. Your solution almost resolved my problem. Tell me, is it possible to filter the last change of issue within selected sprint. In your ex:
OD-16 - show only Nov 26
OD-18 - show only Nov 28
…
OD-23 - show only Nov 22
etc.
After that, I would like to filter these issues to show only unresolved issues.
Reards,
@Bartosz
P.S.1. I’ve forgot about status to show. I’ve prepared measure as Historic Priority in your example, but measure show me current status.
P.S.2.Try to check if all measures works ok, when you select past sprint. Because I see empty cells 
Hi @Bartosz ,
For Status changes, you have to use the Transition Status dimension instead of Status in a calculated measure similar to the one shared previously. I also suggest updating the measure “Issues history” condition to be greater or equal to zero in all the calculated measures.
Now, to see only the last date the changes happened, define a new calculated measure with the formula below:
CASE WHEN
DateCompare(
DateWithoutTime([Time].CurrentHierarchyMember.StartDate),
DateWithoutTime(TimeStampToDate(
MAX(
-- retrieve the latest timestamp of transitions in fileds
{([Measures].[Transition to last timestamp],[Transition Field].[Priority],[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition to last timestamp],[Transition Field].[Resolution],[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition to last timestamp],[Transition Field].[Status],[Time].CurrentHierarchy.DefaultMember)}
)
))
) = 0
THEN 1
END
Then you can filter the report rows by the new calculated measure. See an example below:
Best,
Roberts // support@eazybi.com
Hi @roberts.cacus,
is it possible to add worklog date also as a stamp of last issue change in ‘Last Transition’ measure?
The second thing, can I fill gaps of ‘Remaining estimated hours history’ and ‘Original estimated hours Default’ as a last changed detected values? Ex. for OD-12 issue. ‘Remaining estimated hours history’ is empty, but I would like to see real remaining estimated time (also zero if remianing time is exhausted). Third point. Some time nothing happens with issue during sprint and then Last Transition measure doesn’t set 1. So, when I set filter on my report to see all unresolved issues, my some issues (not changed during sprint) disappear.
Regards, Bartosz
Hi @Bartosz ,
Last Worklog
eazyBI doesn’t have an out-of-the-box way to return the last worklog date. One way I can think of is defining a new calculated measure with the formula below:
-- Last Worklog
DateToTimestamp(Tail(
Filter(
[Time].[Day].Members,
[Measures].[Hours spent] > 0
),1
).Item(0).StartDate)
Unfortunately, that means reworking the calculated measure “Last Transition”, as it won’t work by simply adding the new calculated measure into the set. For this, define three new calculated measures for the last transitions to Priority, Status, and Resolution. See an example for “Last Transition Priority” below:
([Measures].[Transition to last timestamp],
[Transition Field].[Priority],
[Time].CurrentHierarchy.DefaultMember)
Remaining estimated history
You can replace empty values with a zero with the CoalesceEmpty() function. See an example on our documentation page - CoalesceEmpty
Some issues disappear with filter
You can create a new calculated measure that returns the date the issue was added to the Sprint. Then, you can include it in the “Last Transition” calculated measure. With the filter for “Last Transition”, you would see the issues on the date added to the Sprint. The formula could look similar to the one below:
// Last Transition Sprint
( [Measures].[Transition to last timestamp],
[Transition Field].[Sprint status],
[Sprint Status].[Active],
[Sprint].CurrentHierarchyMember
)
Finally, the formula for the “Last Transition” calculated measure formula could look similar to the one below:
CASE WHEN
DateCompare(
DateWithoutTime([Time].CurrentHierarchyMember.StartDate),
DateWithoutTime(TimeStampToDate(
MAX({
[Measures].[Last Transition Priority],
[Measures].[Last Transition Resolution],
[Measures].[Last Transition Status],
[Measures].[Last Worklog],
[Measures].[Last Transition Sprint]
})
))
) = 0
THEN 1
END
Best,
Robert // support@eazybi.com