How to calculate the difference between end of quarter and issue created date

Questions & Answers
1

Hi,

I am buliding two KPI’s reports

  • Quarterly KPI report for employee

    Assignee “Average Resolution Days” “Assignee KPI”

  • KPI per milestone (per issue)
    Milestone Assignee Resolution days “Milestone KPI”

Where every employee is assigned one or more milestone every quarter, so to implement the above reports I need to calculate:

      -  the "Resolution Days", 
         which is the difference between the closed date and the create date  for every milestone
    
     -  "Average Resolution Days" per assignee
       Which is the average resolution days of the assignee milestones

     - "Assignee KPI"
        Which is the average KPI for all his miestones

KPI calculation
“Resolution Days” = Difference between the end of the quarter and the closed date
KP if “Resolution Days”
If resolution days < = 5 days KPI equal 10
If resolution days > 5 days and <= 10 days then KPI equal 7
else KPI equal 5

If milestone not closed (Closed date is empty I think) then kpi = 0

Thanks

2

Hi,

I suspect “milestone” is an issue.
For Assignee quarterly report, user Assignees in the report rows.
For the milestone report, use Issues in the report rows.
The measure and filter for both reports will be the same.

Measures in columns:

  1. Resolution Days and Average resolution days
    Consider which event -issue closing or issue resolution - suits for your case more. Then go for standard measure “Average closing days” or “Average resolution days”: for issues, it would show closing/resolution days for each issue, for assignee report, it would show average closing/resolution time among assignee’s issues.
  2. Assignee KPI
    For that, create a calculation that iterates through all relevant issues (created within the selected period and related to the assignee and report Page selections), retrieves their closing days, maps to KPI and then calculates the average.
    Use CASE WHEN construction to define KPI value accordingly to the days’ difference between issue creation and closing days (or selected quarter start date and closing day). Days difference between any two days calculate using DateDiffDays() function.

Example of such formula to calculate average KPI based on issue closing days:

Avg(
   Filter(
 Descendants([Issue].CurrentMember, [Issue].[Issue]),
 DateInPeriod(
     [Issue].CurrentMember.get("Created at"), [Time].CurrentHierarchyMember)
 AND
 ([Measures].[Issues created],
  [Time].CurrentHierarchyMember.DefaultMember)>0
 ),
  CASE WHEN
   IsEmpty([Issue].CurrentHierarchyMember.get("Closed at"))
  THEN Val(0)
  WHEN
   DateDiffDays([Issue].CurrentHierarchyMember.get("Created at"), [Issue].CurrentHierarchyMember.get("Closed at"))>0 AND
   DateDiffDays([Issue].CurrentHierarchyMember.get("Created at"), [Issue].CurrentHierarchyMember.get("Closed at"))<=5
   THEN Val(10)
  WHEN
   DateDiffDays([Issue].CurrentHierarchyMember.get("Created at"), [Issue].CurrentHierarchyMember.get("Closed at"))>5 AND
   DateDiffDays([Issue].CurrentHierarchyMember.get("Created at"), [Issue].CurrentHierarchyMember.get("Closed at"))<=10
  THEN Val(7)
  WHEN
   DateDiffDays([Issue].CurrentHierarchyMember.get("Created at"), [Issue].CurrentHierarchyMember.get("Closed at"))>10
  THEN Val(5)
 END
)

Ilze, support@eazybi.com