Start date of sub-task as start date of Epic

Questions & Answers
1

Hi, I have list of all epics and all epics have three sub-tasks, showing the start date and due date for different phase of development(Dev, SIT,UAT).
I want to create a report where I can show these dates at epic level in bar chart along with story points resolved so far(defined at story level under Epic).

Any help would be appreciated.

2

Hi @arihantjn20,

For your report, you might want to use Issue dimension with Epic hierarchy to represent individual epics.

To retrieve and show the start and the due dates of Dev, SIT, and UAT subtasks at the epic level. Create a new calculated measure in Measures for each date - it would be calculated measures to show dates as each of three phases (Dev, SIT, UAT) have two dates (start and due).
The expression to get a sub-task date might look like this:

Format(
  Filter(
    --get issues matching subtak keys assigned to epic issue
    [Issue].[Issue].GetMembersByKeys([Measures].[Issue sub-task keys]),
    /*set filtering criteria to identify a subtask representing phase Dev, SIT or UAT. 
    In this example is used issue type for each pahse*/
    [Measures].[Issue type] = "Dev" AND
    --sub-task has due date
    NOT IsEmpty([Issue].CurrentMember.Get('Due date'))
  --get the due date of the Dev sub-task
  ).Item(0).Get('Due date'),
"mmm dd yyyy")

The given expression contains a filtering criterion to identify sub-task representing each phase. The example above has validation by issue type (assuming there are separate issue types for each phase), but, in your case, it might be some other issue property.

The next step is to get the accumulated value of Story Point value on a certain date. For example, to get accumulated SP resolved on the date when the Dev phase is due, you might want to aggregate “Story Points resolved” for all the past periods till Dev due date. The expression might look like this:

Sum(
  --all periods till due date
  {PreviousPeriods(
    [Time].[Day].DateMember([Measures].[Dev phase due date])
  )},
  --acumulate value for resolved SP
  [Measures].[Story Points resolved]
)

Best,
Zane / support@eazyBI.com

3

Woah, looks so simple and logical. Thanks for your help.

1 Like
4

Hi @zane.baranovska

I have a similar kind of requirement where there are 3 sub-tasks for different phase of development in the Parent issue’s life-cycle.

Need to retrieve start & end date for each of these sub-tasks using calculated measures.

1 of these 3 sub-tasks have unique IssueType names (Dev). The above code seems promising.
The other 2 sub-tasks use a standard Sub-task, but using a customfield value for identification between SIT & UAT.

How do we include customField value to the MDX code?

5

@Samuel7, you can adjust the filter criteria for retrieving the date of specific sub-task.
For example,

Format(
  Filter(
    --get issues matching subtak keys assigned to epic issue
    [Issue].[Issue].GetMembersByKeys([Measures].[Issue sub-task keys]),
    /*set filtering criteria to identify a subtask representing phase Dev, SIT or UAT. 
    In this example is used issue type Sub-task and custom field value for SIT*/
    [Measures].[Issue type] = "Sub-task" AND
    [Measures].[Issue <CF field name>] = "CF value for SIT" AND
    --sub-task has due date
    NOT IsEmpty([Issue].CurrentMember.Get('Due date'))
  --get the due date of the Dev sub-task
  ).Item(0).Get('Due date'),
"mmm dd yyyy")

Best,
Zane / support@eazyBI.com

1 Like
6

Awesome @zane.baranovska,

Thanks a lot, it works :slight_smile:

Is it possible to exclude a list of subtasks, so that from the leftover subtasks under the parent, MDX should be able pull these subtasks.

For example, 5 subtasks will mandatorily be created under each parent, out of which 3 default subtasks (UAT/sit/dev) created for all parent issues.

The remaining 2 subtasks will be created from 50 possible options (based on selection from custom field: Task Type). It might not be possible to create 50 columns for all these subtasks. Instead, we want to create a single column under the name: Development.
This development column should exclude looking out for these 3 default subtasks, and should navigate for the remaining 2 subtasks.

Logic: the start date should be captured from the first created subtask out of these 2.

Is it possible to establish one such logic?

Thanks in advance for your kind support!
Samuel.

7

@zane.baranovska kindly verify if the below logic is correct

IIF(
Count(
Filter(
[Issue].[Issue].GetMembersByKeys([Measures].[Issue sub-task keys]),
– cond 1: must be a subtask
[Measures].[Issue type] = “Sub-task”
– tasktype shouldn’t be blank, also eliminate known issues

AND NOT (
[Measures].[Issue Task Type] = “SIT”
OR [Measures].[Issue Task Type] = “UAT”
OR [Measures].[Issue Task Type] = “CIS”
OR [Measures].[Issue Task Type] = “SRD”
OR [Measures].[Issue Task Type] = “BRD”
OR [Measures].[Issue Task Type] IS NULL)

  AND NOT IsEmpty([Issue].CurrentMember.Get('Due date'))
  
 
)

) = 0,
“”,
– hmm, lets get the dates from the remaining subtaks
Format(
Head(
Order(
Filter(
[Issue].[Issue].GetMembersByKeys([Measures].[Issue sub-task keys]),
NOT IsEmpty([Issue].CurrentMember.Get(‘Due date’))
AND NOT (
[Measures].[Issue Task Type] = “SIT”
OR [Measures].[Issue Task Type] = “UAT”
OR [Measures].[Issue Task Type] = “CIS”
OR [Measures].[Issue Task Type] = “SRD”
OR [Measures].[Issue Task Type] = “BRD”
OR [Measures].[Issue Task Type] IS NULL)
),
[Issue].CurrentMember.Get(‘Created at’),
BASC
),
1
).Item(0).Get(‘Due date’),
“mmm dd yyyy”
)
)Preformatted text