Issues data latest on specific date

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 :frowning:

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