Forum Discussion

Birdjo's avatar
Birdjo
Icon for Resolver II rankResolver 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 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!

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

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Helper I rankHelper 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!