Can I duplicate my work hour formula in Eazy BI

Questions & Answers
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