Contact: 514-605-7112 / info@lecfomasque.com
Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 28 mai 2020

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

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

 

kim

Kim Leblanc détient un BAA en Gestion de l’information et des systèmes de l’Université de Sherbrooke. Elle cumule 20 ans d’expérience dans la préparation de rapports et l’automatisation de leur mise à jour éliminant les tâches récurrentes sans valeur ajoutée. Elle est aujourd’hui consultante en intelligence d’affaires et propriétaire de Logika intelligence d'affaires (www.logikaintelligence.com). Elle élabore différents rapports sous Excel et Power BI ainsi que des bases données sous Access pour aider à la prise de décisions et à la gestion de projets. Elle est également formatrice et collaboratrice pour Le CFO Masqué.

  • réponse Emilie BEL ,

    Bonjour, je n’ai pas la proposition 00:00:00 dans la mise en forme

    • réponse Kim Leblanc ,

      Bonjour, vous pouvez l’inscrire directement dans la case même s’il n’est pas dans les choix du menu déroulant.

    Répondre à Kim Leblanc Annuler