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,
Here is a sample dataset:
It contains clock-ins and clock-outs of employees.
For some shifts like for Employee ID = 001, the shift starts at let's say 15:00 and ends at 00:00 so he clocks-out on the next date.
I need a calculated column or a measure where if the Entry is later than 14:30:00 and the Exit for that Employee ID in the next date is earlier than 01:00:00 should result in next date's [Exit] value, else shoudl return same dates [Exit] value.
Thank's in advance!
Solved! Go to Solution.
Hi @Birdjo,
Based on my test, you should be able to use the formula below to create a calculate column to calculate shift exit time in your scenario.
Shift Exit = IF ( Table1[Entry] > TIME ( 14, 30, 0 ) && Table1[Exit] < TIME ( 1, 0, 0 ), CALCULATE ( MAX ( Table1[Exit] ), FILTER ( ALL ( Table1 ), Table1[Date] = EARLIER ( Table1[Date] ) + 1 && Table1[Employee ID] = EARLIER ( Table1[Employee ID] ) ) ), Table1[Exit] )
Regards
Hi @Birdjo,
Based on my test, you should be able to use the formula below to create a calculate column to calculate shift exit time in your scenario.
Shift Exit = IF ( Table1[Entry] > TIME ( 14, 30, 0 ) && Table1[Exit] < TIME ( 1, 0, 0 ), CALCULATE ( MAX ( Table1[Exit] ), FILTER ( ALL ( Table1 ), Table1[Date] = EARLIER ( Table1[Date] ) + 1 && Table1[Employee ID] = EARLIER ( Table1[Employee ID] ) ) ), Table1[Exit] )
Regards
Building on this, I was hoping you might be able to help me with a similar problem I am having regarding assigning an end date. I am trying to track advertising end dates by referencing the next date an ad will run in the same source but I can't figure out how to do this with a formula.
I have inserted a sample table of my data below. I have created a Run Number column which is simply a ranking of oldest to run date to newest run date in each source. As long as the run number is greater than 1, then I want the end date to equal the adjusted run date equal to the run number minus one. I tried to achieve this with the following formula:
End Date = IF('Table1[Run Number]>1,CALCULATE(MIN(Table1[Actual Run Date]),FILTER(ALL(Table1),Table1[Next Run Number]+1=EARLIEST(Table1[Run Number])&&Table1[Source]=Table1[Source])))
However, the dates I getare not matching up with the adjusted run date equal to the next lowest run number.
Source | Creative Name | Adjusted Run Date | Run Number | End Date |
A | H | 12/31/2016 0:00 | 6 | |
A | L | 2/10/2017 0:00 | 5 | |
A | H | 3/24/2017 0:00 | 4 | |
A | H | 4/7/2017 0:00 | 3 | |
A | H | 4/14/2017 0:00 | 2 | |
A | H | 5/7/2017 0:00 | 1 | |
B | L | 2/12/2017 0:00 | 9 | |
B | H | 3/7/2017 0:00 | 8 | |
B | W | 3/12/2017 0:00 | 7 | |
B | L | 3/19/2017 0:00 | 6 | |
B | K | 4/4/2017 0:00 | 5 | |
B | W | 4/18/2017 0:00 | 4 | |
B | H | 4/23/2017 0:00 | 3 | |
B | H | 4/25/2017 0:00 | 2 | |
B | K | 5/4/2017 0:00 | 1 | |
C | Y | 2/13/2017 0:00 | 12 | |
C | W | 2/20/2017 0:00 | 11 | |
C | L | 3/16/2017 0:00 | 10 | |
C | H | 3/17/2017 0:00 | 9 | |
C | W | 3/20/2017 0:00 | 8 | |
C | Y | 3/21/2017 0:00 | 7 | |
C | H | 4/3/2017 0:00 | 6 | |
C | H | 4/11/2017 0:00 | 5 | |
C | W | 4/17/2017 0:00 | 4 | |
C | W | 4/25/2017 0:00 | 3 | |
C | H | 5/3/2017 0:00 | 2 | |
C | H | 5/11/2017 0:00 | 1 | |
D | H | 1/22/2018 0:00 | 16 | |
D | T | 2/13/2018 0:00 | 15 | |
D | T | 2/22/2018 0:00 | 14 | |
D | H | 2/26/2018 0:00 | 12 | |
D | T | 2/26/2018 0:00 | 12 | |
D | T | 2/27/2018 0:00 | 11 | |
D | H | 3/5/2018 0:00 | 9 | |
D | W | 3/5/2018 0:00 | 9 | |
D | H | 3/6/2018 0:00 | 7 | |
D | W | 3/6/2018 0:00 | 7 | |
D | H | 3/19/2018 0:00 | 6 | |
D | F | 4/2/2018 0:00 | 5 | |
D | F | 4/3/2018 0:00 | 4 | |
D | H | 4/9/2018 0:00 | 3 | |
D | H | 4/16/2018 0:00 | 2 | |
D | T | 4/24/2018 0:00 | 1 |
Any help would be much appreciated!
Up!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |