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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
cartolana
Frequent Visitor

Deadlines within workhours and workdays

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.😅

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_1-1657612843444.png

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)

vstephenmsft_0-1657612834351.png

vstephenmsft_2-1657612890029.png

 

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] ) )
    )
)

 

vstephenmsft_4-1657613121265.png

 

 

 

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.

 

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_1-1657612843444.png

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)

vstephenmsft_0-1657612834351.png

vstephenmsft_2-1657612890029.png

 

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] ) )
    )
)

 

vstephenmsft_4-1657613121265.png

 

 

 

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.

 

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'

cartolana_0-1657822839656.png

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?

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

amitchandak
Super User
Super User

@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...

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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