Forum Discussion

user023's avatar
user023
Frequent Visitor
3 years ago

Measure on date based on start and end time

Hi, I have data like this. 

I would like to have a between date slicer and from the min and max date to take total actual running time. 

Also, I would like it to consider the workcenter number (will have it as filter also)

 

ReportedDateStartTimeEndTimeOrderNumberWorkCenterNumberActualRunningTime (Hour)
11-04-202311-04-2023 23:2612-04-2023 0:01Break time0101-1010.583333333
11-04-202311-04-2023 23:2611-04-2023 23:59Break time0101-1010.56
12-04-202312-04-2023 8:0112-04-2023 10:05Other0101-2012.066666667
12-04-202312-04-2023 21:3216-04-2023 21:32No Schedule0101-10196
12-04-202312-04-2023 13:2612-04-2023 14:05Break time0101-2010.65

 

Example : 

If I filter date between 11-04-2023 until 11-04-2023 with work center 0101-101 

Result should be like below.  

ReportedDateStartTimeEndTimeOrderNumberWorkCenterNumberActualRunningTime (Hour)
11-04-202311-04-2023 23:2611-04-2023 23:59Break time0101-1010.56

 

If I filter date between 12-04-2023 until 13-04-2023 with work center 0101-101 & 0101-201

Result should be like below. 

ReportedDateStartTimeEndTimeOrderNumberWorkCenterNumberActualRunningTime (Hour)
12-04-2023 12-04-2023 00:0012-04-2023 0:100Break time0101-1010.00001
12-04-202312-04-2023 8:0112-04-2023 10:05Other0101-2012.066666667
12-04-202312-04-2023 21:3212-04-2023 11:59No Schedule0101-10196
12-04-202312-04-2023 13:2612-04-2023 14:05Break time0101-2010.65

 

In short, I would like sum actual running hour that fall within my selected date

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    user023 , Try a measure like with slicer on independent date table

     

    //Date1 is independent Date table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = minx(allselected(Date1),Date1[Date])
    return
    calculate( sum(Table[Value]), filter('Table', 'Table'[End Time] >=_min && 'Table'[Start Time Time] <=_max))

  • user023's avatar
    user023
    Frequent Visitor
    3 years ago

    Hi, 

    I already try above example but sum the running time will getting wrong result example if I filter date 12/6/2023 my start date 10/6/2023 and end date 12/6/2023 will be listed. (actual running time will be 48hrs) but with my filter it should show only 24hour. 

     

     

    • user023's avatar
      user023
      Frequent Visitor
      3 years ago

      So I am thinking something like, do checking if there are cross day between starttime and endtime  automatic take max date to minus starttime (but result also might not very correct what if starttime 2days back) 

       

      Duration III =

      var maxdate = MAXX(ALLSELECTED('Calendar'), 'Calendar'[DateTime])

      var mindate = MINX(ALLSELECTED('Calendar'), 'Calendar'[DateTime])

      var datediff = DATEDIFF(mindate, maxdate, day)  

      var time = if(datediff > 0 , maxdate - starttime, sum(Results[ActualRunningTime]) )

      return CALCULATE( SUM(time),

      filter ( Results, Results[EndTime] >= mindate

      &&

      Results[StartTime] <= maxdate ))