Count the number of issues on weekly basis relative to milestone

Hi,

We are trying to generate charts for created issues relative to milestone date. There is no field to indicate this, it’s just a date. For example for data:

image

we need to generate data which looks like this:

image

Can someone suggest how to achieve this?

Thanks in advance,
Vrukesh

Hi @vrukesh

The solution would be calculating each weeks position agains the milestone and add cumulative sum over number or created issues.

  1. While you can’t change the time dimension itself (you may want to use Time weekly hierarchy in the report rows), you can create a measure (in Measures) that would mark the week as “Milestone” if the milestone date is within the week, and then calculates the position of the rest of weeks. The formula would be the following (notice, in my calculation, the Milestone is hardcodded date (“2018-02-20”), you must use yours):

     Case WHEN
      DateInPeriod(
      "2018-02-20",
      [Time].CurrentHierarchyMember)
     THEN
     "Milestone"
     ELSE
      case when 
     Int(
       DateDiffDays(
       [Time.Weekly].[Week].DateMember("2018-02-20").StartDate,
       [Time].CurrentHierarchyMember.StartDate)
     /
     7) < 0
     THEN
      "Milestone"|| CAST(Int(
       DateDiffDays(
       [Time.Weekly].[Week].DateMember("2018-02-20").StartDate,
       [Time].CurrentHierarchyMember.StartDate)
     /
     7)AS STRING)
     ELSE
      "Milestone"||"+"|| CAST(Int(
       DateDiffDays(
       [Time.Weekly].[Week].DateMember("2018-02-20").StartDate,
       [Time].CurrentHierarchyMember.StartDate)
     /
     7)AS STRING)
     END
     end
    

Use this measures in the report columns as the first one!

  1. Add measure “Issues created” in columns. To get the cumulative sum, use option Add calculated --> Cumulative sum --> With empty over the measure “Issues created”. Read more information how to apply standard calculations.

  2. Finally, set cell conditional formatting to colorcode value Milestone (use Milestone as both min and max value).

The final report would look the following:

Best,

Ilze / support@eazybi.com