Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone, this is my first post
i'm currently working on Power Bi for some simple reports that i need, im manipulating dates.
the problem is: i have a Initial date, but from that date i need to sum a time that can be minutes to a lots of hours, and the work hours of one day is 7:30 A.M to 17:30 P.M. The weekend and holidays must be validated also.
i read some post in community.powerbi , with the calendar + holidays table, but i can solve my problem at all, ths is the post i read https://community.powerbi.com/t5/Desktop/Add-working-days-to-a-date/td-p/146945
An example may be:
InitialDate WorkHours FinalDate
12/4/2018 17:20:00 P.M 7:50:00 12/5/2018 15:10:00 P.M
then, i have this problem, can you help me with this? thanks in advance
Solved! Go to Solution.
Hi @Anonymous,
You can try to use following calculate column formula to calculate end date based on 'initial date' and 'work hours':
EndDate = VAR _todayRemain = TIME ( 17, 30, 00 ) - MAX ( TIME ( 7, 30, 00 ), MIN ( TIME ( 17, 30, 00 ), TIMEVALUE ( [InitialDate] ) ) ) VAR _totalWorkhours = IF ( WEEKDAY ( [InitialDate], 2 ) <= 5, [Work Hours] - _todayRemain, [Work Hours] ) VAR _workDays = IF ( _totalWorkhours > 0, INT ( _totalWorkhours / 10 ) + IF ( MOD ( _totalWorkhours, 10 ) > 0, 1, 0 ), 0 ) VAR _remaintime = _totalWorkhours - INT ( _totalWorkhours / 10 ) * _totalWorkhours VAR _holidayoffset = IF ( WEEKDAY ( [InitialDate], 2 ) >= 5, 7 - WEEKDAY ( [InitialDate], 2 ), 0 ) RETURN DATEVALUE ( [InitialDate] ) + _holidayoffset + _workDays + TIME ( 7, 30, 0 ) + _remaintime
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can try to use following calculate column formula to calculate end date based on 'initial date' and 'work hours':
EndDate = VAR _todayRemain = TIME ( 17, 30, 00 ) - MAX ( TIME ( 7, 30, 00 ), MIN ( TIME ( 17, 30, 00 ), TIMEVALUE ( [InitialDate] ) ) ) VAR _totalWorkhours = IF ( WEEKDAY ( [InitialDate], 2 ) <= 5, [Work Hours] - _todayRemain, [Work Hours] ) VAR _workDays = IF ( _totalWorkhours > 0, INT ( _totalWorkhours / 10 ) + IF ( MOD ( _totalWorkhours, 10 ) > 0, 1, 0 ), 0 ) VAR _remaintime = _totalWorkhours - INT ( _totalWorkhours / 10 ) * _totalWorkhours VAR _holidayoffset = IF ( WEEKDAY ( [InitialDate], 2 ) >= 5, 7 - WEEKDAY ( [InitialDate], 2 ), 0 ) RETURN DATEVALUE ( [InitialDate] ) + _holidayoffset + _workDays + TIME ( 7, 30, 0 ) + _remaintime
Regards,
Xiaoxin Sheng
How can I add 25 hours of working time to that? I tried using the value '25' in space [Work Hours] but didn't work. Also tried Time(25, 00, 00) but none of them made it ok.
Example: 28/07/2023 16:23:55 -> 02/08/2023 10:59:55
Any help?
hi @v-shex-msft Xiaoxin
Thank you, it worked!! excelent answer!
a time ago a wasn't able to reply because of work, but now i appreciate you help!!! 😄
What is your data source? Is it import or DirectQuery?
is and excel sheet, a column names "FechaInicial" and "TiempoHabil" is given to me to do the calculation
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |