Additionner des données d’heures dans Power BI

Publié le 28 mai 2020
par Kim Leblanc BAA

Une question a été posée récemment sur le forum 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. Cet article vise à répondre à cette question.

 

Exemple d’addition des heures sous analyse

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.

Addition des heures

 

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.

 

Un mot sur le mode de stockage des données dans Excel

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:

 

Ajout d’une colonne de date de comparaison

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

 

Calcul d’une durée pour faciliter l’addition des heures

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:

 

La durée en secondes facilite largement l’addition des heures. Nous sommes donc prêts pour charger le tout dans Power BI et créer notre mesure.

 

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

 

Deuxième étape: Création d’une mesure pour l’addition des heures

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.

 

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

Addition des heures

 

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

Addition des heures

 

La durée

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

 

Les heures

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

 

Les minutes

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

 

Les secondes

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

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

 

Préparartion de la mesure pour l’addition des heures

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:
Addition des heures

 

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.

Addition des heures

 

On obtient alors le résultat souhaité.

Addition des heures

 


Fichier d’accompagnement VIP à télécharger

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

 

Formation complémentaire

Pour une introduction au langage DAX, utilisé par Power Pivot et par Power BI Desktop, qui permet de créer des tableaux de bord flexibles et faciles à mettre à jour en plus de créer des visualisations de données évoluées et pertinentes, suivez la formation Introduction au langage DAX (Power BI et Power Pivot).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Le-CFO-masque_Commentaires-Introduction-langage-DAX

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

18 réflexions sur “Additionner des données d’heures dans Power BI”

  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

  3. Bonjour, j’ai pas pu appliquer le format d’affichage suivant 00:00:00 dans la section Mise en forme.
    Le résultat correcte est 90:00:00 => mais affiché sur Power BI 900000, et la section de la mise en forme est désactivé.
    merci de votre aide!!

    1. Bonjour,
      Quand vous dites que la section mise en forme est désactivée, c’est qu’elle est grisée et que vous ne pouvez pas y entrer de l’information?
      Est-ce que c’est possible que dans votre formule DAX vous passiez le résultat en format texte? En utilisant des & par exemple?

      Il m’est difficile de répondre sans voir votre modèle de données et votre mesure. Si vous le souhaitez, vous pourriez déposer votre ficher avec votre question dans la section Forum. nous pourrons vous aider à trouver la solution.

      https://www.lecfomasque.com/forums

      Kim

  4. Bonjour,merci pour votre réponse!
    En fait moi j’ai tout fait comme indiqué dans la solution que vous avez déposé ci-dessus.
    Je vais déposer mon problème dans la partie Forums avec le fichier .pbix sous le nom Problématique de mise en forme 00:00:00.
    Merci!

  5. Bonjour, merci votre réponse.
    pour bien vous expliquer ,tout d’abord j’ai une colonne où il y a des durées de format Datetime exemple (1899/12/31 00:02:30) et je dois faire la somme de toutes les durées saisies dans cette colonne.
    J’ai suivi les étapes indiqués dans ce blogue ci-dessus:

    Alors les mesures que j’ai fait:
    Date comparaison c’est une colonne personnalisée date comparaison= #date(1899,12,31) pour obtenir seulement le time qui sera stocké dans la colonne nommée Durée (type de la colonne est durée).
    Puis colonne Durée en secondes = Duration.TotalSeconds([Durée]) type de la colonne entier.
    Après j’ai crée les mesures suivantes:
    SommeDuree = SUM(‘mapping'[Duree en secondes]) => type de colonne nombre entier
    Heures = INT ([SommeDuree]/3600)
    Minutes = INT(MOD([SommeDuree]-([Heures]*3600);3600)/60)
    Secondes = MOD(MOD([SommeDuree]-([Heures]*3600);3600);60)
    NbHeures = [Heures]*10000 + [Minutes]*100 + [Secondes] => c’est une mesure pour calculer le nombre des heures total qui est normalement 90:00:00.

    Merci pour votre aide.

  6. Bonjour Tous le monde,
    J’ai testé cette fonction est ça marche bien.
    Mais, peux tu nous donner le détail comment utiliser cette astuce avec DAX et pas avec Power Query.
    J’ai une mesure à créer à partir d’une colonne déjà créée avec DAX .Y a t-il une possibilité d’afficher “48:00:00” avec DAX ?

    Merci d’avance

  7. Bonjour,
    Merci pour votre solution, elle est claire.
    Par contre mon total est supérieur à 24h, le total est de plusieurs jours : est-ce possible d’afficher un format en Jours puis heure ? ou bien d’afficher 432:00:00 ? car cela ne s’affiche pas correctement chez moi.
    Merci pour votre aide

    1. Bonjour,
      Le principe serait le même pour afficher en jours en ajoutant une étape pour calculer le nombre de jours. Ça donnerait ceci:

      ***
      NbrJourheureFormat =
      VAR Duree = SUM(Tableau1[DureeCommunicationSecondes])
      // Calcul le nombre d’heures, il y a 3600 secondes dans une heure – On utilise INT pour convertir au nombre entier
      VAR JOUR = INT(Duree/(3600*24))
      VAR Heures =INT((MOD(Duree-(JOUR*3600*24),3600*24))/3600)
      // Calcul le nombre de minutes – On utiliser MOD pour aller chercher le reste suite à une division.
      //On peut ensuite diviser le reste par 60 pour obtenir le nombre de minute
      VAR Minutes = INT((Duree-((Jour*3600*24)+(Heures*3600)))/60)
      VAR Secondes = MOD(MOD((Duree-((Jour*3600*24)+(Heures*3600))),60),60)
      //On place chacun des valeurs au bonne endroit
      VAR RESULT = Jour * 1000000 + Heures * 10000 + Minutes * 100 + Secondes
      RETURN
      RESULT

      ****
      Il faudrait alors indiquer un format comme celui-ci “00 00:00:00” dans le menu outils de mesure.

      Par contre, si vous n’avez pas été en mesure d’afficher le format “432:00:00” vous aurez probablement le même problème. Il est indiqué comment procéder à la troisième étape de l’article.
      Vous pourriez toujours vous abonner VIP et obtenir le fichier d’accompagnement de cet article pour vous aider.
      https://www.lecfomasque.com/abonnement-vip/

      Au plaisir,
      Kim

  8. Bonjour.
    J’ai appliqué la formule que vous avez suggérée pour additionner les heures dans Power BI. Cependant, je fais face à un problème au niveau des minutes. En comparant avec la formule dans Excel, j’ai remarqué un léger écart dans Power BI. Pour le reste, tout semble correct.
    Merci.

  9. Bonjour,
    2023, et cette aide est toujours 100% utile. Merci beaucoup pour ca.

    J’ai une question, quand on essaye de montrer le résultat sous forme de graphique, ce dernier continue de lire la mesure comme un nombre entier. Comment faire pour que l’axe des ordonnées puisse également renvoyer le format 00:00:00 svp ?

    Cordialement,

  10. Nevermind, j’ai trouvé la solution 2 minutes après avoir posté la question, Pourtant j’étais dessus depuis 2h. Ce forum a je pense un pouvoir caché 🙂

    Il faut aller dans format visuel, “Axe y”, “valeurs”, et dans “unité d’affichage”, sélectionner “aucun” au lieu de “automatique”.

Laisser un commentaire

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

Retour en haut