Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
I am going round in circles and need some help please.
I have a machine asset shift column and I need to work out how many hours per shift the machine was working.
If this calculation should only occur if the machine has both an arrive and leave record for the shift.
I have this measure, however it is not taking the MAX arrive - MAX leave time, just any other time records in the shift. The N/A in the arrive column are other activities such as lunch breaks.
Thank you for the help.
Measure:
Hours working=
SUMX(
'Table DRAFT',
IF(
'Table DRAFT'[Arrive/Leave] = "Arrive",
DIVIDE(
DATEDIFF(
MIN('Table DRAFT'[DatetimeStart]),
MAX('Table DRAFT'[DatetimeStart]),
MINUTE
),60
)
))
Screen shots:
Solved! Go to Solution.
Hi, @Bumpy8732
You can try the following methods.
Measure =
Var _minarrive=CALCULATE(MIN('Table'[DatetimeStart]),FILTER(ALL('Table'),[Arrive/Leave]="Arrive"))
Var _maxleave=CALCULATE(MAX('Table'[DatetimeStart]),FILTER(ALL('Table'),[Arrive/Leave]="leave"))
Return
DATEDIFF(_minarrive,_maxleave,MINUTE)/60
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Bumpy8732
You can try the following methods.
Measure =
Var _minarrive=CALCULATE(MIN('Table'[DatetimeStart]),FILTER(ALL('Table'),[Arrive/Leave]="Arrive"))
Var _maxleave=CALCULATE(MAX('Table'[DatetimeStart]),FILTER(ALL('Table'),[Arrive/Leave]="leave"))
Return
DATEDIFF(_minarrive,_maxleave,MINUTE)/60
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Bumpy8732 , Try like
SUMX(
filter( 'Table DRAFT','Table DRAFT'[Arrive/Leave] = "Arrive"),
DIVIDE(
DATEDIFF(
MIN('Table DRAFT'[DatetimeStart]),
MAX('Table DRAFT'[DatetimeStart]),
MINUTE
),60
)
)
Or push a visual row context
calculate(
SUMX(values('Table DRAFT'[Column])
,
DIVIDE(
DATEDIFF(
MIN('Table DRAFT'[DatetimeStart]),
MAX('Table DRAFT'[DatetimeStart]),
MINUTE
),60
)
), filter( 'Table DRAFT','Table DRAFT'[Arrive/Leave] = "Arrive") )
Thank you for your response.
Unfortunately I am still getting a result that is unexpected.
I could only get your first suggestion to display something (sorry I am new to this,) but it is still just taking the first record minus the last one. I need to filter out the N/A's in the query for that to work?
Any Ideas? Thank you 🙂
In this example, I am looking to return a result of 8.5 hours.
This is from 8:15 am from the first "arrive"record and then 4:45pm for the last "leave" record.
A complication with this one is that a leave event is before the arrive. This is because they use the same code to start and end the shift.
Thanks
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |