Forum Discussion

Aurélien's avatar
Aurélien
Frequent Visitor
5 years ago
Solved

Response times

Bonjour,

 

J'ai un problème dans Power BI, je n'arrive pas à calculer les délais d'intervention

J'ai:
  • "La date de création du ticket" au format JJ / MM / AAA HH: MM: SS
  • "Date d'intervention" au format JJ / MM / AAA HH: MM: SS

 

J'utilise la FORMULE DAX: "DATEDIFF" , mais je veux calculer le temps de réponse en "Minute" sans tenir compte des  samedi et dimanche.

 
Exemple:
Date de la demande d'interventionVendredi 03/09/2020 10:30:00
Date d'intervention09/09/2020 15:00:00

Résultat = 6030 MINUTES OU 100,5 HEURES

 

Vous remerciant à l'avance

Au revoir

times
  • lbendlin's avatar
    lbendlin
    5 years ago

    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)

19 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Here is a sample table definition for a table called "Intervention"

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ31jcyMDJQMDSwMjawMjBQ0gGKWULFTIECILHYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}})
in
    #"Changed Type"

     

    And here is the calculated column that calculates the difference in minutes minus your specified weekend days

     

    Difference = 
var db = CALENDAR(Intervention[Start],Intervention[End])
var we = ADDCOLUMNS(db,"WE",if(WEEKDAY([Date],2)>5,1,0))
return DATEDIFF(Intervention[Start],Intervention[End],MINUTE)-1440*sumx(we,[WE])

     

    • lbendlin's avatar
      lbendlin
      Super User
      5 years ago

      Here's a slightly different approach, same result.

       

       

      Difference =
DATEDIFF ( Intervention[Start], Intervention[End], MINUTE )
    - 1440 * SUMX (
            CALENDAR ( Intervention[Start], Intervention[End] ),
            IF ( WEEKDAY ( [Date], 2 ) > 5, 1, 0 )
        )

       

    • Aurélien's avatar
      Aurélien
      Frequent Visitor
      5 years ago

      Bonjour,

      Merci pour cette reponse rapide, mais je ne comprend pas à quoi fait référence cette partie là:

       

      if (WEEKDAY ([Date], 2)> 5,1,0))

       

      Merci par avance, 

      • lbendlin's avatar
        lbendlin
        Super User
        5 years ago

        This marks Saturday and Sunday as 1 and all other weekdays as 0.