Can I duplicate my work hour formula in Eazy BI

Questions & Answers
1

Hello

I am trying to create a report in Easy BI that will compute an SLA that accounts for work hours(8-5) and work days (M-Fr). I can do it in Excel but I am having a problem recreating it in Easy BI.

The formula in Excel is =NETWORKDAYS(E3,F3)9-24(MOD(E3,1)-9/24+18/24-MOD(F3,1))
Note: E3 = create date and F3 = Resolved date

Is there a way to duplicate this formula in Eazy BI since we are not using JIra Service Desk

Thanks

1 Like
2

You can use a similar calculation in MDX as well. There is a bit different way how to address it, but here it comes. I added an Average aggregation over a set of issues to get it working for totaling members as well.

NonZero(AVG(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember
    )),
    CASE WHEN
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.Defaultmember) > 0
THEN
-- ### workhours calculation on Issue level ###
  DateDiffWorkDays(
     DateWithoutTime([Measures].[Issue created date]),
     DateAddDays(DateWithoutTime([Measures].[Issue resolution date]),1)
  )
-- 9 represents working hours in a day and 24 hours are calendars hours in a day
  *9-24*
  (mod(cast(format([Measures].[Issue created date], "###.######") as NUMERIC),1)
-- 9 and 18 represent working time from 9 till 18
  -9/24+18/24-
  mod(cast(format([Measures].[Issue resolution date], "###.######") as NUMERIC),1)
  )
END
))

Daina / support@eazybi.com

1 Like
3

Thank you so much! This works great.

4

Hi @rfire08,

On Cloud and since eazyBI version 6.5, there is a new MDX function DateDiffWorkhours() that calculates the duration between two dates in working hours.
For more details please see the documentation: DateDiffWorkhours

Best,
Zane / support@eazyBI.com