Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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] )
)
)
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] )
)
)
Thanks!
With minor changes i got it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
90 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |