Calculating business hours cycle time in Jira using eazyBI

[omid sh]

Hi everyone,

I am using Jira Software with eazyBI and I want to calculate the cycle time of an issue (Story / Task) from To Do → Done.

The problem is that Jira/eazyBI calculates the full elapsed time (24/7), including nights and non-working hours.

What I need instead is to calculate only business hours, with the following rules:

• Working hours: 08:00 to 16:00
• Time should stop outside business hours and resume the next working day at 08:00 

Unfortunately, in our eazyBI instance:

• I do not have access to configure Business / Working Hours
• The function DateDiffWorkhours is not available

👉 Is there any way to calculate true business-hour cycle time using only MDX formulas in eazyBI, without Jira Service Management SLA or external scripting?if you have any suggestions please let me know.

[Rahul_RVS]

Hi @omid sh 

Welcome to the community !! 

As an alternate solution, without the need of any scrpting, you can try out

Time in Status Reports 

With this app you generate time in each workflow status for multiple issues with multiple filter and grouping options. 

With Status grouping feature, can help you define your own issue age time and view the averages of the same. Also the app has 20+ reports to meet a variety of use cases.

Also the app has a custom calendar option to define your working hours and days which will be taken in calculation for time in status.

More details here.

Disclaimer : I am part of the app team for this add-on

[Iryna Krutko]

Hi @omid sh

Great question - this is a very common challenge. By default, tools like eazyBI calculate cycle time using full elapsed (calendar) time, including nights, weekends, and holidays. Building a reliable business-hours calculation purely with MDX formulas is extremely complex, error-prone, and hard to maintain.

Agile Cycle Time Charts solves this natively with a built-in Work Schedule and Duration calculation method. You can define your exact working hours (e.g., 08:00–16:00), working days (Mon–Fri), breaks, and even holidays - no scripting or JSM SLAs required.

When set to Duration mode, the app automatically pauses the clock outside business hours and resumes the next working day. For example, an issue moving from To Do to Done over a weekend would only count the actual workdays.

Docs: Work Schedule · Calculation Method

Interactive example: Cycle Time Chart - a clickable demo showing how the chart works

Feel free to reach out if you need help with the setup!

[Anastasiia Maliei SaaSJet]

Hі @omid sh 

If you find that the calculation logic in Jira becomes too cumbersome or difficult to maintain, you might want to look at Time Metrics Tracker by SaaSJet.

I’ve seen many teams switch to this tool when they hit limitations with standard reporting for business hour calculations. Here is why it fits your specific case:

• Custom Work Schedules: You can explicitly set your working window to 08:00 – 16:00 and define your weekends and holidays in a simple UI.

• No Code or Complex Formulas Required: It calculates Cycle Time (To Do → Done) automatically based on these business hours, saving you from having to write complex queries.

• Accuracy: It handles the "pause" outside of business hours natively, so you get the exact "active" time the issue spent in progress.

It’s a great alternative if you want to save time on configuring tools and just get the data you need.

Hope this helps! Let us know if you manage to crack the standard settings or if you'd like more details on the automated approach.

[Birkan Yildiz _OBSS_]

Hey @omid sh 

 Welcome to the Community! 

For this exact scenario, using a dedicated app that calculates the data and feeds it directly into eazyBI might be the best approach.

Timepiece - Time in Status for Jira handles this exact calculation seamlessly.

Here is how Timepiece solves your specific requirements:

Accurate Cycle Time: You can easily define and calculate your Agile metrics, like Cycle Time (e.g., from "In Progress" to "Done").

Custom Calendars for Business Hours: This solves your 24/7 problem. You can create a custom calendar set specifically to your 08:00 to 16:00 schedule. Timepiece automatically pauses the timer outside of these hours (and on weekends/holidays) and resumes the next day at 08:00, giving you true business-hour calculations.

Seamless eazyBI Integration: You don't need to struggle with complex MDX formulas. Timepiece has a built-in REST API that integrates directly with eazyBI. You can easily fetch your pre-calculated, calendar-accurate Cycle Time data and use it directly as a data source for your eazyBI dashboards.

Here is an example of what the Cycle Time report looks like:

You can check Timepiece on the Atlassian Marketplace. Hope this helps!

Disclosure: I am part of the OBSS team, the creators of Timepiece.

Best,
Birkan

[Roberts Čāčus - eazyBI]

Hi @omid sh

I recommend using the `DateDiffWorkhours()` function, which is available since eazyBI version 6.5.0, released in June 2022 - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/datediffworkhours.

There, you can specify the working hours and non-working days. In your case, the formula would look similar to the one below:

DateDiffWorkHours(
  [Measures].[Issue created date],
  [Measures].[Issue resolution date],
  'default',
  '08:00-16:00'
)

Use the "decimal" formatting option. The formula uses the issue creation and resolution dates, assuming issues are created with the "To Do" status and resolved once they reach "Done". It would return a result for individual issues.

If the `DateDiffWorkhours()` function is not available for you, please update eazyBI. 

Best,
Roberts // support@eazybi.com

[Jorge Cammarota]

Good evening!

To confirm: you are using Jira Data Center + eazyBI (Server/Data Center version, which uses pure MDX).

The formula I've used in these cases is a valid and classic approach to simulate business hours (08:00–16:00) + excluding weekends using only standard MDX, without depending on the eazyBI Business Hours configuration or any specific work schedule functions.

Strengths of your formula:

Handles the first day and last day separately (cutting off anything outside business hours)
Calculates full business days in between (Monday–Friday only) × 8 hours
Uses DateName("w", ...) to identify Saturday/Sunday (works in English in eazyBI Server MDX)
Converts everything to decimal hours (/ 3600.0)

Common issues that may prevent it from working out of the box (and fixes):

DateName("w", dt) returns numbers, not strings like "Saturday"
In Microsoft-based MDX (used by eazyBI), DateName("w", dt) usually returns: 1 = Sunday, 2 = Monday, …, 7 = Saturday (depending on the server's first-day-of-week setting, but 1=Sunday is common).
Replace the conditions with numbers:
DateName("w", dt) = 1 OR DateName("w", dt) = 7 -- Sunday or Saturday

Or prefer Weekday(dt, 1) (1=Sunday, 7=Saturday).

Format() + DateParse() can fail depending on server locale/regional settings
In some Brazilian servers, "yyyy-MM-dd" works, but "HH:mm" may cause issues.
Safer alternative: use DateAdd + #08:00:00# literal time values instead of string concatenation.

Generate + Filter to count fullBusinessDays
The Generate({1:fullDays}, ...) + Filter + Sum(...,1) approach is creative but can be slow on large cubes (thousands of issues) because it builds a large set.
Faster alternative (if fullDays is not huge):
VAR fullBusinessDays =
DateDiff("d", DateAdd("d",1,DateValue(startDate)), DateValue(endDate))
- (DateDiff("ww", DateAdd("d",1,DateValue(startDate)), DateValue(endDate)) * 2)
- IIF(Weekday(DateAdd("d",1,DateValue(startDate)),1) = 7, 1, 0)
- IIF(Weekday(DateValue(endDate),1) = 1, 1, 0) -- fine-tune for edge cases

tartRaw / endRaw using [Transition to status first time]
Make sure Import options → Transitions is enabled and "Transition to status first time" is imported as a date/time measure.

Recommended adjusted & cleaned-up version (test this one):
-- Business hours cycle time To Do → Done (08:00–16:00, Mon–Fri only)
WITH
MEMBER [Measures].[Business hours cycle time To Do -> Done] AS
NonZero(
IIF(
IsEmpty(([Measures].[Transition to status first time], [Transition Status].[To Do]))
OR
IsEmpty(([Measures].[Transition to status first time], [Transition Status].[Done])),
NULL,
-- Main calculation
VAR startRaw = ([Measures].[Transition to status first time], [Transition Status].[To Do])
VAR endRaw = ([Measures].[Transition to status first time], [Transition Status].[Done])
VAR startDt = Min(startRaw, endRaw) -- ensure correct order
VAR endDt = Max(startRaw, endRaw)

VAR sameDay = (DateValue(startDt) = DateValue(endDt))

VAR sAdj =
IIF(Weekday(startDt, 1) IN {1,7}, NULL, -- Sunday or Saturday
IIF(TimeValue(startDt) < #08:00:00#,
DateValue(startDt) + #08:00:00#,
IIF(TimeValue(startDt) > #16:00:00#,
NULL,
startDt)))

VAR eAdj =
IIF(Weekday(endDt, 1) IN {1,7}, NULL,
IIF(TimeValue(endDt) > #16:00:00#,
DateValue(endDt) + #16:00:00#,
IIF(TimeValue(endDt) < #08:00:00#,
NULL,
endDt)))

-- Same day case
IIF(sameDay,
IIF(IsEmpty(sAdj) OR IsEmpty(eAdj) OR sAdj >= eAdj,
NULL,
DateDiff("n", sAdj, eAdj) / 60.0), -- minutes → decimal hours

-- Multi-day case
VAR firstEnd = DateValue(startDt) + #16:00:00#
VAR firstStart = IIF(TimeValue(startDt) < #08:00:00#,
DateValue(startDt) + #08:00:00#,
startDt)
VAR firstH = IIF(IsEmpty(firstStart) OR firstStart >= firstEnd, 0,
DateDiff("n", firstStart, firstEnd) / 60.0)

VAR lastStart = DateValue(endDt) + #08:00:00#
VAR lastEnd = IIF(TimeValue(endDt) > #16:00:00#,
DateValue(endDt) + #16:00:00#,
endDt)
VAR lastH = IIF(IsEmpty(lastStart) OR lastStart >= lastEnd, 0,
DateDiff("n", lastStart, lastEnd) / 60.0)

VAR daysBetween = DateDiff("d", DateAdd("d", 1, DateValue(startDt)), DateValue(endDt))
VAR fullBDays = daysBetween
- (Floor(daysBetween / 7) * 2)
- IIF(Weekday(DateAdd("d", 1, DateValue(startDt)), 1) = 7, 1, 0)
- IIF(Weekday(DateValue(endDt), 1) = 1, 1, 0)

firstH + lastH + (fullBDays * 8.0)
)
)
)

Usage Tips:

Format → Decimal or Duration
Rows: Problem / Problem Type / Project / etc.

Columns: your new metric

If it still doesn't work (returns NULL, syntax error, incorrect values, etc.), please:

Copy and paste the exact error message displayed by eazyBI

This way, I can optimize it even further.

Sincerely!

[omid sh]

Formula is not valid:
Syntax error at line 2, column 1, token 'WITH'

[Jorge Cammarota]

I'm sorry this error occurred. Let's try the following:

If you are creating a calculated measure, instead of: WITH

MEMBER [Measures].[My Measure] AS ...
SELECT ...

you should only put the measure definition, for example:

Sum(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),

[Measures].[Issues created] > 0

),
[Measures].[Issues created]
)

[omid sh]

Formula is not valid:
Syntax error at line 1, column 1, token 'MEMBER'

[Roberts Čāčus - eazyBI]

The answers suggested by @jorge cammarota seem AI-generated, mixing up multiple MDX-related and unrelated concepts.