Deadlines within workhours and workdays

[cartolana]

I´m trying to generate deadlines for deliverys on a starting point and priority, within working hours and workdays. The delivery deadline is based on a starting point (on this case, when the order is made). To it, i imagined cutting this process into parts : {[(working hours, work days), identify the start point (entry of request)] and combine it to priority}.

For working hours i´ve tried the lines below, but they weren´t enough.
Because of this start between Workinghours*Worktime i can´t move on.

worktime=
VAR afternoon = IF(OR(SELECTEDVALUE('Tabela 2 (Teste dh separadas)'[Entry - H])<TIME(13,00,00),SELECTEDVALUE('Tabela 1 (Teste dh juntas)'[Dia de Entrada])>TIME(17,30,00)),"no","yes")
VAR morning = IF(OR(SELECTEDVALUE('Tabela 2 (Teste dh separadas)'[Entry - H])<TIME(08,30,00),SELECTEDVALUE('Tabela 1 (Teste dh juntas)'[Dia de Entrada])>TIME(12,00,00)),"no","yes")
var hworktime =IF(morning||afternoon, "Worktime","Outoftime")
return
hworktime

Just to give more context 

Workhours morning = 08,30,00 to 12,00,00
workhours afternoon = 13,00,00 to 17,30,00

Priorities and deadline to deliver
LOW = 40 hours
MID = 16 hours
HIGH = 8 hours

My table right now

ORDER | PRIORITY | ENTRY DATE | ENTRY HOURS
123 | LOW | 28/01/2022 | 17:31:00 
456 | LOW | 30/04/2022 | 08:00:00
789 | MID | 03/02/2022 | 12:00:00
098 | HIGH | 06/06/2022 | 10:45:00

My exemple of it´s utility: a order was made at 17:31 on a Friday, low priority. The time would start running only on Monday at 8:30. (On prediction, the deadline would be at Friday of this week, 5 days after (40 hours).

I´m on the initial phases on DAX, we´ve changed recently to it, so i´m sorry for the baby code.😅

[v-stephen-msft · Accepted solution]

Hi @cartolana ,

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.

[cartolana]

Hi! 
This response was amazing, it almost work.
When i applied this solution to my real database (a many rows table, so a many to one relationship with the another table, still both sides) showed that a circular dependency was detect, and it couldn´t work.
When i tried to switch the relationship to many to one (only side) it calculates only one day to the entire 'Date'

Here i have the Entry Date as "Criado em - Data" and the Entry hour as "Criado em - Hora"

Is there some solution possible to this?

[cartolana]

I DID IIT! it was needed to transform the columns formats on Power Query! It worked!

[amitchandak]

@cartolana , As these can we rounded to work day and time can be added again

Se if this can help

refer the code in comment for new column

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[cartolana]

Hi, @amitchandak, thanks for the reply!
I download the archive and read the article, i can view a possibility to implement this model on a phase of the project. But it came with another thought: is it possible to work with time like this? You´ve said it could be added again, but i couldn´t visualize it. Do you have some exemple?