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.
Hi all,
How can create the column 'Break' below?
It calculates the time between the end of the shift and start of the next one. They can be on different dates but have to be the next day worked (date column).
This also has to correlate to Timesheet ID.
Timesheet ID | Date | Start | End | Break |
144699 | 02/09/2020 | 07:30 | 17:00 | 0 |
144699 | 03/09/2020 | 07:30 | 17:00 | 14:30 |
144699 | 06/09/2020 | 07:30 | 17:00 | 14:30 |
144700 | 01/09 | 08:00 | 17:00 | 0 |
144700 | 05/09 | 08:00 | 17:00 | 09:00 |
Solved! Go to Solution.
Hi @HenryJS ,
Start_time =
VAR __start = MAX(Sheet7[Start])
RETURN
DATE(2020, 1, 2) + __start
End_time =
VAR __end =
CALCULATE(
MAX([End]),
FILTER(
ALL(Sheet7),
Sheet7[Timesheet ID] = MAX(Sheet7[Timesheet ID]) && Sheet7[Date] < MAX(Sheet7[Date])
)
)
RETURN
IF(
__end <> BLANK(),
DATE(2020,1,1) + __end,
BLANK()
)
Measure 2 =
IF(
[End_time] <> BLANK(),
[Start_time] - [End_time],
TIME(0,0,0)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @HenryJS ,
Start_time =
VAR __start = MAX(Sheet7[Start])
RETURN
DATE(2020, 1, 2) + __start
End_time =
VAR __end =
CALCULATE(
MAX([End]),
FILTER(
ALL(Sheet7),
Sheet7[Timesheet ID] = MAX(Sheet7[Timesheet ID]) && Sheet7[Date] < MAX(Sheet7[Date])
)
)
RETURN
IF(
__end <> BLANK(),
DATE(2020,1,1) + __end,
BLANK()
)
Measure 2 =
IF(
[End_time] <> BLANK(),
[Start_time] - [End_time],
TIME(0,0,0)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@HenryJS , Try new columns like
create new columns
start time = [Date] + [start]
End time = [Date] + [end]
Break = maxx(flter(Table, [Timesheet ID] =earlier([Timesheet ID]) && [Date]<earlier([date])),[End time]) -[start time]
@amitchandak thanks that works!
However on the first instance it calculates a break time even though there's no shift before?
The one highlighted red should be 0
@HenryJS See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous
In your case you would use DATEDIFF between your row and the previous row once you extract the necessary values.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |