Forum Discussion
Response times
There is a difference between an empty string ("") and no data (null). The COALESCE function only works with the second scenario. If you know that for BP your demand data may be an empty string "" then you need to either adjust the first line, or replace the empty string with BLANK() (which produces a null value)
Super Userplease revalidate your rule/expected outcome for row 4.
Here is the adjusted calculated column after re-reading your explanation.
Difference =
var e = COALESCE(CompteRendu[Date et Heure Demande],UTCNOW())
var d = DATEDIFF(e,CompteRendu[Date et Heure Début],MINUTE)
return if(d<0,0,d-1440*sumx(CALENDAR(e,CompteRendu[Date et Heure Début]),if(WEEKDAY([Date],2)>5,1,0)))
J'ai toujours ce message d'erreur, car lorsque j'ai un "bp" la valeur de "Date et Heure Demande" est vide. Ou parfois il n'y a pas de date de début.
- lbendlin
There is a difference between an empty string ("") and no data (null). The COALESCE function only works with the second scenario. If you know that for BP your demand data may be an empty string "" then you need to either adjust the first line, or replace the empty string with BLANK() (which produces a null value)
Super, cà fonctionne !!! Mais j'ai desormais un autre probléme, je refais un topic.
Ps: cette formule peut elle être adaptée en Power Query ?
- lbendlin
Yes, since the computation happens inside the row it is possible to implement in Power Query. You would need to use the same process - create the list, and then identify sat/sun via https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek
This actually reminds me - what do you want to do when the start or end dates are on weekend days?