Forum Discussion

Birdjo's avatar
Birdjo
Resolver II
8 years ago
Solved

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...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

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. :smileyhappy:

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's avatar
    MikeO
    Helper I
    8 years ago

    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!