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.
PTWY Hrs =
var shiftstart = 6 //shift start at 6am
var shiftend = 17 //shift end at 5pm
var wrkdyhours = shiftend-shiftstart //11 workday hours
var wrkdyafterhours = 24-(shiftend-shiftstart) //13 after shift hours on 11 hour workday
return
if('INBD Items'[Putaway Date].[Date]='INBD Items'[GR Date].[Date],//if putaway done on same day as gr
DATEDIFF('INBD Items'[GR Date],'INBD Items'[Putaway Date],MINUTE)/60,//then calculate the minutes
(DATEDIFF('INBD Items'[GR Date],'INBD Items'[Putaway Date],MINUTE)-(//else calculate minutes and subtract non working hours
CALCULATE(countrows('Date'),DATESBETWEEN('Date'[Date],'INBD Items'[GR Date],'INBD Items'[Putaway Date]),'Date'[IsWorkingDay]=FALSE())*24*60+ //calculate non working day convert to minutes to subtract
(CALCULATE(countrows('Date'),DATESBETWEEN('Date'[Date],'INBD Items'[GR Date],'INBD Items'[Putaway Date]),'Date'[IsWorkingDay]=TRUE())-1)*wrkdyafterhours*60 //calculate after hour minutes to subtract
))/60//convert to hours
)
Hi @Anonymous ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.
Best Regards
Lucien
Hi @Anonymous ,
It is possible to add calculated columns or do some actions in query editor when there's a direct query connection. But there're some limitations for it. Below is the articles about these limitations for your reference.
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
http://radacad.com/directquery-live-connection-or-import-data-tough-decision
Best Regards,
Lucien
@Anonymous What about converting it to a measure? Then you won't have those restrictions.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |