cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

Get value for next date based on a condition

Hello,

Here is a sample dataset:
shift exits.png

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
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. Smiley Happy

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]
)

c1.PNG

 

Regards

View solution in original post

4 REPLIES 4
Highlighted
Resolver II
Resolver II

Re: Get value for next date based on a condition

Up!

Highlighted
Microsoft
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. Smiley Happy

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]
)

c1.PNG

 

Regards

View solution in original post

Highlighted
Resolver II
Resolver II

Re: Get value for next date based on a condition

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

Highlighted
Helper I
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.

 

SourceCreative NameAdjusted Run DateRun NumberEnd Date
AH12/31/2016 0:006 
AL2/10/2017 0:005 
AH3/24/2017 0:004 
AH4/7/2017 0:003 
AH4/14/2017 0:002 
AH5/7/2017 0:001 
BL2/12/2017 0:009 
BH3/7/2017 0:008 
BW3/12/2017 0:007 
BL3/19/2017 0:006 
BK4/4/2017 0:005 
BW4/18/2017 0:004 
BH4/23/2017 0:003 
BH4/25/2017 0:002 
BK5/4/2017 0:001 
CY2/13/2017 0:0012 
CW2/20/2017 0:0011 
CL3/16/2017 0:0010 
CH3/17/2017 0:009 
CW3/20/2017 0:008 
CY3/21/2017 0:007 
CH4/3/2017 0:006 
CH4/11/2017 0:005 
CW4/17/2017 0:004 
CW4/25/2017 0:003 
CH5/3/2017 0:002 
CH5/11/2017 0:001 
DH1/22/2018 0:0016 
DT2/13/2018 0:0015 
DT2/22/2018 0:0014 
DH2/26/2018 0:0012 
DT2/26/2018 0:0012 
DT2/27/2018 0:0011 
DH3/5/2018 0:009 
DW3/5/2018 0:009 
DH3/6/2018 0:007 
DW3/6/2018 0:007 
DH3/19/2018 0:006 
DF4/2/2018 0:005 
DF4/3/2018 0:004 
DH4/9/2018 0:003 
DH4/16/2018 0:002 
DT4/24/2018 0:001 

 

 

Any help would be much appreciated!

Helpful resources

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