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.
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
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 |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |