Forum Discussion
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.😅
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.
5 Replies
- amitchandak
Super User
Anonymous , 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-Calendar-4-5-Power/ba-p/1187766Hi, 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?
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.
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?
