Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Birdjo
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
v-ljerr-msft
Employee
Employee

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
v-ljerr-msft
Employee
Employee

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

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!

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

Birdjo
Resolver II
Resolver II

Up!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.