Forum Discussion

Dan_SP's avatar
Dan_SP
Regular Visitor
5 years ago

Split duration into hourly basis and also split dates when the process overflows the next day

Hi All, I have a problem where in need to split the start and end time in hourly basis and find the duration for which the machine was busy, below is the sample data which i have. Preferably need th...
Need Help
Show and Tell
v-henryk-mstf's avatar
v-henryk-mstf
Icon for Community Support rankCommunity Support
5 years ago

Hi Dan_SP ,


After reading your question, I am a little confused. I want to know what the calculation logic is in the screenshot below. The same result cannot be obtained with End-Start in the marked lines. I hope you can provide detailed data information and expected results to facilitate my further testing.

 


Best Regards,
Henry

 

  • Dan_SP's avatar
    Dan_SP
    Regular Visitor
    5 years ago

    Hi v-henryk-mstf ,

    PFA screenshot with new start and end time columns which I added for your reference, basically I won't it to split into hourly duration and find run time within that hour.

    So if you see for key x3 its start time is 2020-01-17T22:45:00 and end time is 2020-01-17T23:05:00 so I would want to split the data to show that the machine M2 has run for 15mins in the 22nd hour and for 5 mins in the 23rd hour, thats the part I am looking for. Hope it answers your question.

     

     

    Regards,

    Dan

    • v-henryk-mstf's avatar
      v-henryk-mstf
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Dan_SP ,


      Thank you for the accurate data and information you provided me in your reply. In this regard, I have done a test as a reference: first use append in Power Query to merge the two tables, and create two measures to find the duration and hours that are met under different conditions. Create a slicer measure to filter out different rows of hour, and get the following results:

       

      Hour_Reuslt = 
VAR cur_index =
    MIN ( 'Table'[Index] )
VAR cur__S_hour =
    SUM ( 'Table'[S_Hour] )
VAR cur_E_hour =
    SUM ( 'Table'[E_Hour] )
VAR next_index =
    CALCULATE (
        SUM ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Key] = MAX ( 'Table'[Key] )
                && 'Table'[Index] > cur_index
        )
    )
RETURN
    IF (
        cur__S_hour == cur_E_hour,
        SUM('Table'[S_Hour]),
        IF (
            SUM ( 'Table'[Index] ) < next_index,
            cur__S_hour,
            cur_E_hour)
    )
      Min_Reuslt = 
VAR cur_index =
    MIN ( 'Table'[Index] )
VAR cur__S_hour =
    SUM ( 'Table'[S_Hour] )
VAR cur_E_hour =
    SUM ( 'Table'[E_Hour] )
VAR next_index =
    CALCULATE (
        SUM ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Key] = MAX ( 'Table'[Key] )
                && 'Table'[Index] > cur_index
        )
    )
RETURN
    IF (
        cur__S_hour == cur_E_hour,
        SUM ( 'Table'[E_Min] ) - SUM ( 'Table'[S_Min] ),
        IF (
            SUM ( 'Table'[Index] ) < next_index,
            60 - SUM ( 'Table'[S_Min] ),
            SUM ( 'Table'[E_Min] )
        )
    )
      Slicer_ = 
VAR cur_index =
    MIN ( 'Table'[Index] )
VAR cur__S_hour =
    SUM ( 'Table'[S_Hour] )
VAR cur_E_hour =
    SUM ( 'Table'[E_Hour] )
VAR next_index =
    CALCULATE (
        SUM ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Key] = MAX ( 'Table'[Key] )
                && 'Table'[Index] > cur_index
        )
    )
RETURN
    IF(cur__S_hour==cur_E_hour && cur_index <next_index,1,0)

      Here is the sample pbix file.


      If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.


      Best Regards,
      Henry


      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Dan_SP's avatar
        Dan_SP
        Regular Visitor
        5 years ago

        Hi v-henryk-mstf 

         

        Sorry for the delayed response.

        Thanks for your help. I think this should work, let me check and get back to you.

         

        Regards,

        Dan