Get value for next date based on a condition

[Birdjo]

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!

[v-ljerr-msft · Accepted 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

[MikeO]

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!

[Birdjo]

@v-ljerr-msft, thank you very much!

[Birdjo]

Up!