Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Get value for next date based on a condition

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Birdjo

Resolver II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
12:22 AM

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

v-ljerr-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
10:42 PM

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
##

Birdjo

Resolver II

Re: Get value for next date based on a condition

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
05:51 AM

Up!

Highlighted

v-ljerr-msft

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-27-2017
10:42 PM

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
##

Birdjo

Resolver II

Re: Get value for next date based on a condition

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2017
12:13 AM

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

Highlighted
##

MikeO

Helper I

Re: Get value for next date based on a condition

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-12-2018
12:23 AM

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!

Top Solution Authors

User | Count |
---|---|

412 | |

313 | |

81 | |

56 | |

46 |

Top Kudoed Authors

User | Count |
---|---|

564 | |

502 | |

162 | |

141 | |

128 |