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 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.

 

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.

 

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

 

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:

 

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é.

 

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 masqué - Commentaires formation - Introduction au langage DAX (Power BI et Power Pivot)

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é.

12 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

  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

Laisser un commentaire

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

Scroll to Top