cancel
Showing results for
Did you mean:
Highlighted
Resolver II

## Get value for next date based on a condition

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Get value for next date based on a condition

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

4 REPLIES 4
Highlighted
Resolver II

Up!

Highlighted
Microsoft

## Re: Get value for next date based on a condition

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

Highlighted
Resolver II

## Re: Get value for next date based on a condition

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

Highlighted
Helper I

## Re: Get value for next date based on a condition

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!

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors