cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

``````CALENDAR =
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.

5 REPLIES 5
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.

``````CALENDAR =
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.

Frequent Visitor

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?

Frequent Visitor

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

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

Frequent Visitor

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors