Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |