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.
como calcular o tempo tirando final de semana, feriado e considerado apenas das 8 às 18.
Há casos que acontecem da noite para o dia, por exemplo:
Neste caso, entrou na sexta e saiu na segunda, para considerar 02:28 min, e na sexta são 2 horas para as 18 e na segunda saiu com 28 min. Saída de entrada 23/12/2021 16:00 25/12/2021 8:28
neste caso tenho que considero apenas 28min, pois o horário do horário das 8 às 18 eu desconsidero Saída de entrada 28/12/2021 18: 46 29/12/2021 8:28
Neste caso considere 3 horas Saída de entrada
27/12/2021 9:00 27/12/2021 12:00
Solved! Go to Solution.
Net Work Days - Microsoft Power BI Community
Net Work Duration (Working Hours) - Microsoft Power BI Community
Hi @Diego09
You need to have a Date table in which there is a column indicating whether a date is a working day or holiday. Then create a new column with below DAX to get the net work hours. You can download the attachment to see details. Hope it helps.
Net Work Hours =
var _day1 = MINX(FILTER('Date','Date'[Date]>='Table'[Check-In].[Date]&&'Date'[IsWorkingDay]=1),'Date'[Date])
var _day2 = MAXX(FILTER('Date','Date'[Date]<='Table'[Check-Out].[Date]&&'Date'[IsWorkingDay]=1),'Date'[Date])
var _nonWorkDays = COUNTROWS(FILTER('Date','Date'[Date]>_day1&&'Date'[Date]<_day2&&'Date'[IsWorkingDay]=0))
var _inDay_IsWorkDay = MAXX(FILTER('Date','Date'[Date]='Table'[Check-In].[Date]),'Date'[IsWorkingDay])
var _outDay_IsWorkDay = MAXX(FILTER('Date','Date'[Date]='Table'[Check-Out].[Date]),'Date'[IsWorkingDay])
var _inTime = IF(_inDay_IsWorkDay=1,TIMEVALUE(FORMAT('Table'[Check-In],"h:mm:ss")),TIME(8,0,0))
var _outTime = IF(_outDay_IsWorkDay=1,TIMEVALUE(FORMAT('Table'[Check-Out],"h:mm:ss")),TIME(18,0,0))
var _startTime = TIME(8,0,0)
var _endTime = TIME(18,0,0)
var _fullHours = (18-8)
var _day1Hours = MAX((_endTime-MAX(_inTime,_startTime))*24,0)
var _day2Hours = MAX((MIN(_outTime,_endTime)-_startTime)*24,0)
var _netWorkHours = IF(DATEDIFF(_day1,_day2,DAY)<0,0,(DATEDIFF(_day1,_day2,DAY)-1-_nonWorkDays)*_fullHours+_day1Hours+_day2Hours)
return
_netWorkHours
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Net Work Days - Microsoft Power BI Community
Net Work Duration (Working Hours) - Microsoft Power BI Community
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.