Deadlines within workhours and workdays

Hi Anonymous ,

It really took me a long time.

Here's my solution to calculate the deadline datetime.

1.Create a deadline hours table by entering data, a calendar table by dax.

CALENDAR = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
    "Workday", IF ( WEEKDAY ( [Date], 2 ) IN { 6, 7 }, "N", "Y" )
)

Rank = RANKX(FILTER('CALENDAR',[Workday]="Y"),[Date],,ASC,Dense)

2.Create a calculated column to calculate the approximate deadlines first.

Date =
VAR _RANK =
    RELATED ( 'CALENDAR'[Rank] ) + RELATED ( 'Table (2)'[DAYS] )
RETURN
    CALCULATE ( MAX ( 'CALENDAR'[Date] ), FILTER ( 'CALENDAR', [Rank] = _RANK ) )
        + TIME ( 17, 30, 0 )

3.Create a calculated column to calculate the number of hours left until the end of the day's work.

HOURS REMAINING =
IF (
    [ENTRY HOURS] <= TIME ( 12, 0, 0 ),
    [ENTRY HOURS] - TIME ( 8, 30, 0 ),
    IF (
        [ENTRY HOURS] > TIME ( 12, 0, 0 )
            && [ENTRY DATE] < TIME ( 13, 0, 0 ),
        TIME ( 3, 30, 0 ),
        [ENTRY HOURS] - TIME ( 9, 30, 0 )
    )
)

4.The final result is then calculated according to different situations.

Deadline DateTime = 
IF (
    WEEKNUM ( [ENTRY DATE] )
        IN { 6, 7 }
            || [ENTRY HOURS] >= TIME ( 17, 30, 0 ),
    [Date],
    IF (
        [HOURS REMAINING] > TIME ( 4, 30, 0 ),
        [Date]
            - TIME ( HOUR ( [HOURS REMAINING] ) + 1, MINUTE ( [HOURS REMAINING] ), SECOND ( [HOURS REMAINING] ) ),
        [Date]
            - TIME ( HOUR ( [HOURS REMAINING] ), MINUTE ( [HOURS REMAINING] ), SECOND ( [HOURS REMAINING] ) )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.