Calculation of total time based custom dates & custom time Fields

Questions & Answers
, , ,
1

I am trying to calculate Total Impact time based custom dates & custom time.
We have Custom Field named as Outage Level with two options Continuous & Specific
Custom Date Fields Impact Start Date, Impact End Date
Custom Time Fields Impact Start Time, Impact End Time

if Outage Level is Continuous we need to calculate total days from Impact Start Date to Impact End Date
if Outage Level is Specific we need to calculate total hours from the Impact Start Time to Impact End Time and need to multiply with total days from Impact Start Date to Impact End Date

Ex : Outage Level - Specific
Impact Start Date - 01-Aug-23
Impact End Date - 03-Aug-23
Impact Start Time - 09.00 AM
Impact End Date - 11.00 AM
Total Impact time will be 3 Days*2 Hours = 6 Hours

Outage Level - Continues
Impact Start Date - 01-Aug-23
Impact End Date - 03-Aug-23
Total Impact time will be 3 Days*8 Hours = 24 Hours

Calculating Total Impact for Continuous Impact Level we are using below measure and it is working fine
coalesceEmpty(Sum(
Filter(
DescendantsSet(
[Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues created] > 0
),
DateDiffWorkHours(
[Measures].[Issue Impact Start Date],
[Measures].[Issue Impact End Date],
‘default’,
‘09:00 - 17:00’
)
),0)

Thanks & Regards
Raju

2

Impact Start Time, Impact End Time are Single Line Text Custom Fields

3

Hi @Nagaraju_T ,
DateDiffWorkHours() expects input data to be in the format as date with time component (e.g. 2023-09-25 17:14:00) and thus it is working fine when you calculated your continuous impact.

When text fields are used as input data, these solutions become susceptible to human errors such as typos, unsupported time formats, and unexpected input data. But if you have a process in place to validate the input data, then you can try this solution:

  1. Create start time for calcultation using the first date (so the input data would have also date component, not only time):
--measure name "Start time (for calculation)"
CASE WHEN
  NOT IsEmpty([Measures].[Issue Start date])
  AND
  NOT IsEmpty([Measures].[Issue Start time (time component)])
THEN
  DateParse(
  Format([Measures].[Issue Start date], "yyyy-mm-dd") || " " ||
  [Measures].[Issue Start time (time component)])
END
  1. Create similar measure for end time, this and the measure above is needed to calculate outage hours:
--measure name "End time (for calculation)"
CASE WHEN
  NOT IsEmpty([Measures].[Issue Start date])
  AND
  NOT IsEmpty([Measures].[Issue End time (time component)])
THEN
  DateParse(
  Format([Measures].[Issue Start date], "yyyy-mm-dd") || " " ||
  [Measures].[Issue End time (time component)])
END
  1. Then add this together in formula where you calculate the hours and then multiply them by days for “Outage Level - Specific”:
-- calculate outage duration (for hours)
DateDiffHours(
  [Measures].[Start time (for calculation)],
  [Measures].[End time (for calculation)]
)
*
-- calculate outage duration (for days)
DateDiffDays(
  DateWithoutTime([Measures].[Issue Start date]),
  DateAddDays(
    DateWithoutTime([Measures].[Issue End date]),
    1)
)
  1. The report would look like this:
    image
    image2078×632 82.1 KB

best,
Gerda // support@eazybi.com