Power BI – Vous rencontrez des problèmes avec l’addition des heures?

Publié le 28 mai 2020
par Kim Leblanc

Power BI – Vous rencontrez des problèmes avec l’addition des heures?

Une question a été posée récemment sur le blogue concernant l’addition des heures. La personne voulait calculer le nombre d’heures de communications effectuées par des techniciens. Le résultat devant être sous le format suivant hh:mm:ss ou plus concrètement 54:34:55 pour un résultat de 54 heures, 34 minutes et 55 secondes.

Dans l’exemple ci-dessous, les données sources proviennent d’un fichier Excel et elles sont sous format DATE HEURE. Il faudra faire un peu de bricolage pour utiliser ces données et en arriver à un cumul d’heures…

Pour se faire, on devra passer par Power Query pour calculer le temps en secondes puis utiliser ce résultat dans une mesure en DAX et lui appliquer un format d’affichage approprié.

Voici un aperçu des données qui proviennent d’un fichier Excel.

Voici le résultat souhaité dans Power BI:

Première étape, transformer les données de durée en secondes.

Les données sont sous le format hh:mm:ss sous Excel. Par contre, si on regarde de plus près, on remarque que ces heures sont en fait considérées comme des dates. Si on se déplace sur la cellule pour Mathieu au 30 avril, la valeur est 24:08:02, mais si on regarde dans la barre de formule, on peut voir que cette valeur est considérée comme une date soit le 1er janvier 1900 à minuit 8 minutes et 12 secondes ceci explique que lors de l’importation dans Power Query, ces différentes valeurs apparaissent comme des dates.

Excel stocke les dates sous forme de numéros de série séquentiels afin qu’elles puissent être utilisées dans les calculs. Par défaut, le 1er janvier 1900 correspond au numéro séquentiel 1, ici comme nos données sont des heures, lorsqu’elles sont inférieures à 24 heures, le numéro séquentiel se trouve plutôt à être 0 donc converti comme une journée avant le 1er janvier 1900 donc le 31 décembre 1899. Voici le résultat une fois dans Power Query:

Pour être en mesure de calculer la durée en secondes, on pourra en premier lieu ajouter une colonne personnalisée contenant la date du 31 décembre 1899 et puis modifier le type pour Date/Heure

Ensuite, il faudra soustraire nos deux dates: [Durée Communication Mois]-[Date Comparaison] et lui attribuer un type de données Durée

Puis transformer la durée en secondes à l’aide de la fonction Duration.TotalSeconds.

Une fois le type de données modifié pour nombre entier et les colonnes superflues supprimées, on obtient le résultat suivant:

Nous sommes donc prêt pour charger le tout dans Power BI et créer notre mesure.

 

Deuxième étape: Création de la mesure dans Power BI

Une fois les données chargées dans Power BI, on créé une mesure pour calculer le nombre d’heures à partir de la colonne DureeCommunicationSecondes.

Le mesure contient les 4 variables suivantes: Duree, Heures, Minutes et Secondes.

Voici le comportement de ces différentes variables en utilisant l’exemple de Mathieu pour lequel il y a 290 663 secondes.

La variable Duree calcule la somme de la colonne DureeCommunicationSecondes
Duree = SUM(Tableau1[DureeCommunicationSecondes])
Duree = 290 663

La variable Heures calcule le nombre d’heures en divisant par 3 600 puisqu’il y a 3 600 secondes dans une heure. On encadre le tout par INT pour arrondir au nombre entier vers le bas. (notez qu’il faudrait plutôt utiliser TRUNC si nous avions des valeurs négatives puisque INT de -3,6 par exemple, donnerait un résultat de -4)

Heures = INT(Duree/3 600)
Heures = INT(290 663/3 600)
Heures = INT(80,739722)
Heures = 80

Pour calculer le nombre de minutes, il faut d’abord obtenir le reste c’est à dire le 0,739722 que nous n’avons pas pris en considération dans le calcul d’heures précédent (pour avoir le nombre entier – INT).
On utilisera la fonction MOD pour aller chercher le reste puis INT pour arrondir le reste au nombre entier le plus bas.
La fonction MOD contient deux paramètres: un Nombre (la durée restante en secondes après la soustraction des heures calculées dans la 1ere variable Heures) et un Diviseur (3 600 secondes)
On divisera ensuite par 60 pour convertir le reste d’heures en minutes.

Minutes = INT(MOD(Duree-(Heures*3600);3600)/60)
Minutes = INT(MOD(290 663-(80*3600);3600)/60)
Minutes = INT(2663/60)
Minutes = INT(44,38)
Minutes = 44

On utilisera ensuite le même principe pour le calcul des secondes

Secondes = MOD(MOD(Duree-(Heures*3600);3600);60)
Secondes = 23

On pourra alors placer nos Heures, Minutes et Secondes au bon endroit avec le calcul suivant:

Heures * 10000 + Minutes * 100 + Secondes
80*10000 + 44 *100 + 23
800000 + 4400 + 23
804423

Mais ce n’est pas tout, si on laisse notre mesure telle quelle, on obtient le résultat suivant:

Troisième étape: appliquer un format d’affichage

Il faut ensuite sélectionner notre mesure et à partir du menu Outils de mesure, appliquer le format d’affichage suivant 00:00:00 dans la section Mise en forme.

On obtient alors le résultat souhaité.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
 


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

5 réflexions sur “Power BI – Vous rencontrez des problèmes avec l’addition des heures?”

  1. Bonjour, si j’ai besoin de calculer moyenne, à la place de la somme, dois-je remplacer « SUM » par « AVERAGE » dans ma mesure initiale?

    j’essaie de calculer la durée moyenne de conversation en minutes et secondes (00:00:00) des employée du call center ou je travaille mais, je n’arrive pas.

    merci de votre aide!!

  2. Bonjour,
    Oui, dans l’exemple donné dans l’article, vous pouvez remplacer la fonction SUM par AVERAGE, mais si vous n’y arrivez pas, vous pourriez déposer votre ficher avec votre question dans la section Forum. nous pourrons vous aider à trouver la solution.

    Au plaisir,

    Kim

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top