Forum Discussion
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. :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
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
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!