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 (wil...
Need Help
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 ))