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
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.
[Measures].[Issue resolution date],
[Time].CurrentHierarchy.Defaultmember) > 0
-- ### workhours calculation on Issue level ###
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
(mod(cast(format([Measures].[Issue created date], "###.######") as NUMERIC),1)
-- 9 and 18 represent working time from 9 till 18
mod(cast(format([Measures].[Issue resolution date], "###.######") as NUMERIC),1)
Daina / email@example.com
Thank you so much! This works great.
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
Zane / support@eazyBI.com