Problem with hour calculation

Question

Person id&nbsp; &nbsp; &nbsp; &nbsp; Index 1&nbsp; &nbsp; &nbsp; &nbsp;Code&nbsp; &nbsp; &nbsp; &nbsp; Work Order&nbsp;&nbsp;&nbsp;&nbsp; Index 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; date/time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; in/out calculation1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;in&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;in1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WO100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 103&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;in&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WO106&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 104&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;in&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WO197&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 105&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;in1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;51&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;out&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;106&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;in&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Person id&nbsp; &nbsp; &nbsp; &nbsp; Index 1&nbsp; &nbsp; &nbsp; &nbsp;Code&nbsp; &nbsp; &nbsp; &nbsp; Work Order&nbsp;&nbsp;&nbsp;&nbsp; Index 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; date/time&nbsp; &nbsp; &nbsp; in/out calculation1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;in&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; out&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WO100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 103&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; out1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WO106&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 104&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; out1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WO197&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 105&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; out1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;51&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&lt;&gt;out&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 106&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; out&nbsp;&nbsp;Index 1: Combined column from two tables, Codes and Work Orders. Index2 includes all data rows, ordered by combination of person id and date/time.&nbsp;&nbsp;Codes table contains all actions when personnel is stamping in/out, Work orders contains used work order numbers during the day (between in and out stampings).&nbsp;First example is where person has done right, in stamp – 3 x WO’s – out stamp. In this case all lines must be tagged as “in calculation” somehow.&nbsp;Second example is where person missed out stamp, in that case (in – wo’s – out – chain is broken) all lines must be tagged as “out of calculation” somehow.&nbsp;In other words, correct chain is Code in – x Work Orders – Code out, where x &gt;= 0&nbsp;So, daily “in calculation” data is included in metric and used to calculate working hours and how many hours of these working hours are assigned to WO’s. “out of calculation” data is excluded from metric. Hour calculation is done by date/time data.&nbsp;In and out stamps are easy to find and tag “in calculation” but all Work Order lines between correct in/out stamps are not solved.&nbsp;if code is “in” (index1 = 50, index2=102) and next code is “out” (index1=51, index2=106) then all lines between index2=102 and index2=106 must be tagged as “in calculation”.&nbsp;Else all lines must be tagged as “out of calculation”. &nbsp;&nbsp;Any Ideas?&nbsp;

v-juanli-msft · Accepted Answer

Hi&nbsp;JARONN&nbsp;
If i understand you correctly,&nbsp;
first add a index column in Edit queries,
then create calculated columns in Data model view,
out =
CALCULATE (
    LASTNONBLANK ( Sheet1[Code], 0 ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[person id ] ),
        Sheet1[Index 1]
            = EARLIER ( Sheet1[Index 1] ) + 1
            &amp;&amp; Sheet1[Index] &gt; EARLIER ( Sheet1[Index] )
    )
)

condition = IF([out]="out","in",IF([out]="&lt;&gt;out","out"))

in/out cal =
CALCULATE (
    LASTNONBLANK ( Sheet1[condition], 0 ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[person id ] ),
        Sheet1[Index] &lt;= EARLIER ( Sheet1[Index] )
    )
)

Best RegardsMaggie
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Problem with hour calculation

2 Replies

Related content

Problem with calcul

Calculation problem

Logic problem?

Problem calculating flowfield in DAX

Slider problems