Problem with hour calculation

[JARONN]

Person id        Index 1       Code        Work Order     Index 2            date/time        in/out calculation

1                     50               in                                     102                  xxxxx               in

1                     21                               WO100            103                  xxxxx               in                        

1                     22                               WO106            104                  xxxxx               in                        

1                     23                               WO197            105                  xxxxx               in

1                     51               out                                   106                  xxxxx               in                        

Person id        Index 1       Code        Work Order     Index 2              date/time      in/out calculation

1                     50               in                                     102                      xxxxx            out                     

1                     21                               WO100            103                      xxxxx            out

1                     22                               WO106            104                      xxxxx            out

1                     23                               WO197            105                      xxxxx            out

1                     51               <>out                              106                      xxxxx            out

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. 

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).

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.

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.

In other words, correct chain is Code in – x Work Orders – Code out, where x >= 0

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.

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.

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”.

Else all lines must be tagged as “out of calculation”.  

Any Ideas? 

[v-juanli-msft · Accepted solution]

Hi @JARONN 

If i understand you correctly, 

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
            && Sheet1[Index] > EARLIER ( Sheet1[Index] )
    )
)

condition = IF([out]="out","in",IF([out]="<>out","out"))

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

Best Regards
Maggie

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

[JARONN]

Thanks!

With minor changes i got it.