Count the number of first transitions to Done each month

[Daniel Tobler]

Hi

I would like to display the number of Done Epics (Features in SAFe-Terminology) per month. Unfortunately, Epics could be opened later again for some field updates. But only the first transition should be taken into account!

I'm selecting the measure "Transitions to status issues count" and list it monthly. But the report counts every transition, not just the first one. I've tried with "Transition to status first date", but cannot add the returned dates.

How can I filter transitions to only include the first transition?

{

  "cube_name": "Issues",

  "cube_reports": [ {

     "name": "Feature Velocity Test",

     "result_view": "table",

     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Transitions to status issues count]"],"members":[]},{"name":"Transition Status","selected_set":["[Transition Status].[Done]"],"members":[],"bookmarked_members":[{"depth":1,"name":"Done","full_name":"[Transition Status].[Done]"},{"depth":1,"name":"Releasing","full_name":"[Transition Status].[Releasing]"},{"depth":1,"name":"Deploying to Production","full_name":"[Transition Status].[Deploying to Production]"},{"depth":1,"name":"Validation on Staging","full_name":"[Transition Status].[Validation on Staging]"},{"depth":1,"name":"Implementing","full_name":"[Transition Status].[Implementing]"},{"depth":1,"name":"Refined/Ready","full_name":"[Transition Status].[Refined/Ready]"},{"depth":1,"name":"Analyzing","full_name":"[Transition Status].[Analyzing]"},{"depth":1,"name":"Clarified","full_name":"[Transition Status].[Clarified]"},{"depth":1,"name":"Clarifying","full_name":"[Transition Status].[Clarifying]"},{"depth":1,"name":"Funnel","full_name":"[Transition Status].[Funnel]"}]},{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false,"removed":true}],"bookmarked_members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[Month].Members"],"members":[],"bookmarked_members":[]}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Issue Type","selected_set":["[Issue Type].[Epic]"],"members":[{"depth":1,"name":"Epic","full_name":"[Issue Type].[Epic]"}],"bookmarked_members":[{"depth":1,"name":"Epic","full_name":"[Issue Type].[Epic]"}],"current_page_members":["[Issue Type].[Epic]"]},{"name":"Resolution","selected_set":["[Resolution].[All Resolutions]"],"members":[{"depth":0,"name":"All Resolutions","full_name":"[Resolution].[All Resolutions]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Gelöst","full_name":"[Resolution].[Gelöst]","parent_full_name":"[Resolution].[All Resolutions]"}],"bookmarked_members":[],"current_page_members":["[Resolution].[Gelöst]"]}]},"options":{"nonempty":true},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[{"dimension":"Measures","name":"Test","formula":"(\n  [Measures].[Transition to status first date],\n  [Transition Status].[Done]\n)","format_string":""}]}

  } ],

  "calculated_members": [{"dimension":"Measures","name":"Transition to status first date","format_string":"yyyy-mm-dd hh:nn:ss","formula":"TimestampToDate(\n  [Measures].[Transition to status first timestamp]\n)"}]

}

[Nauris Malitis -eazyBI-]

Hi @Daniel Tobler 

You can define a new measure in the Measures dimension and use the following formula:

Count(
  Filter(
    Descendants([Issue.Epic].CurrentMember,[Issue.Epic].[Epic]),
    DateInPeriod(
      (
        [Measures].[Transition to status first date],
        [Transition Status].[Done],
        [Time].CurrentHierarchy.DefaultMember
      ),
      [Time].CurrentHierarchyMember
    )
  )
)

This formula will iterate through all of the imported Epics and find the first transition date to Done status from all times and then check if this date falls into the selected Time period in the report.

The number of Epics that match this criteria will be counted.

Be sure to set the Formatting to Numeric -> Integer for this measure.

Let me know if this works as expected!

​Best regards,

​Nauris

[Valerie Knapp]

Hi @Daniel Tobler , welcome to the Atlassian Community and thanks for your question. 

This code you have shared here is from eazybi, correct? 

I agree that if anything is possible, it will need to be using some kind of reporting app. I think, however, this question about feasibility would be best directed to the vendor, which you can do by opening a ticket to them - https://marketplace.atlassian.com/apps/1211051/eazybi-reports-and-charts-for-jira 

There is also a link there to their documentation but for something this specific, I would just open a ticket to ask. 

Best wishes