Cumuls des Heures

Accueil – Le CFO masqué Forums Power Query Cumuls des Heures

Mots-clés : 

10 sujets de 1 à 10 (sur un total de 10)
  • Auteur
    Articles
  • #63637
    philippe.moncler
    Participant

    Bonjour à tous,

    Je cherche à cumuler des heures et à afficher un résultat supérieure à 23:59:59
    Par exemple 28:30:00.
    Ma base de départ est un fichier Excel qui stocke les horaires en décimal. La mise en forme des cellules dans Excel permet la personnalisation de type : [h]:mm:ss ce qui permet d’afficher des cumuls d’heures.
    En revanche dans la vue PowerBI/Modele /le mise en forme de mon Champ ( qui contient le horaire) ne permet pas cette syntaxe. J’ai beau tout essayer , et consulter mon forum Favori 😉 rien ne permet un affichage correct.
    PowerBi stocke les variables en DateHeure et je ne sais pas comment soit contourner le problème soit transformer correctement mes valeurs.

    Mon objectif étant de cumuler des heures de travail effectuées sur un maximum de 4 digits ( ex : 1200:30:00)

    lundi => 7:30
    Mardi => 7:30
    Mercredi=> 6:30
    etc…

    Merci pour vos conseils et recommandations

    @ Bientôt, Philippe.

    Attachments:
    You must be logged in to view attached files.
    #63639
    Sophie Marchand
    Participant

    Bonjour,

    C’est toujours très difficile d’aider quelqu’un sans avoir accès à son fichier et sans savoir quel est le but utlime.

    Ne sachant pas, je me demande ici si le fait que Power Query n’affiche pas le tout comme vous le désirez est vraiment important et si une fois dans votre tableau croisé dynamique, vous pouvez afficher le tout correctement…

    Sinon, une piste à explorer serait sans doute de fractionner chaque champ en heures, minutes, secondes et de les cumuler séparément, puis de les additionner par la suite.

    Mais encore une fois, n’ayant ni accès à votre fichier, ni accès au résultat final recherché, ce n’est pas facile de vous aider.

    Pour une aide plus efficace, partagez avec nous le fichier et indiquez-nous précisément le résultat recherché.

    Merci.

    #63640
    philippe.moncler
    Participant

    Bonjour Sophie,

    En effet avec les exemples c’est plus simple.

    Voici donc le relevé d’activité d’un Call Center. ( sur deux mois )
    Dans les colonnes J et K s’affiche ( sous excel ) la somme des durées d’appels mensuels. Ces chiffres dépassent facilement la limite de 24h.
    Je souhaiterais mettre en forme de rapport ( simple tableau ) ces chiffres, MAIS PowerBI transforme les valeurs supérieures à 24.

    Soit si “Bernard” a travaillé 26:30 le résultat affiché dans mon rapport sera : 2:30.
    J’espère avoir pu vous fournir suffisamment d’éléments pour avancer sur ma problématique avec vous.

    Merci pour votre retour et la qualité de vos échanges.

    Cordialement,

    Philippe.

    Attachments:
    You must be logged in to view attached files.
    #63645
    Kim Leblanc
    Participant

    Bonjour,

    Voici la solution que je vous propose.

    En premier lieu, dans Power Query, enlever le format date pour mettre un format heure.
    Ensuite ajouter 3 colonnes à partir de votre colonne d’heure en utilisant le menu heure (ajouter Heure, minutes et secondes).
    Vous pourrez alors ajouter une colonne pour additionner les 3 colonnes heures, minutes et secondes en prenant soin de multiplier les heures par 3600, les minutes par 60 pour obtenir le total de secondes.

    Vous pourrez alors vous créer une mesure sur cette colonne du type:
    DureeGlobal =
    VAR Duree = SUM([Duree_secondes])
    // Calcul le nombre d’heures, il y a 3600 secondes dans une heure
    VAR Heure = INT ( Duree / 3600)
    // Calcul du nombre de minutes
    VAR Minute = INT ( MOD( Duree – ( Heure * 3600 );3600 ) / 60)
    // Calcul du nombre de secondes
    VAR Seconde = MOD ( MOD( Duree – ( Heure * 3600 );3600 ); 60 )
    RETURN
    // On place chacune des valeurs au bon endroit
    Heure * 10000 + Minute * 100 + Seconde

    Puis sélectionner votre mesure et appliquer un format personnalisé dans le menu Outils de mesure en écrivant 00:00:00 dans la boite de format.

    Ci-joint un fichier avec la solution.

    Au plaisir,

    Kim

    Attachments:
    You must be logged in to view attached files.
    #63647
    philippe.moncler
    Participant

    Bonjour Kim,

    merci pour cette proposition, que je vais tester de ce pas.
    C’est tout de même surprenant que la gestion des heures dans BPI ne soit pas plus “accessible”.
    Je ne manquerais pas de revenir vers vous pour vous tenir informer.
    Encore merci pour tous ces efforts et votre proposition très complète.

    Cordialement,

    Philippe,

    #63673
    philippe.moncler
    Participant

    Bonjour à tous,

    Bonjour Kim,

    je n’avais malheureusement pas de PJ dans l’email, j’ai donc suivi scrupuleusement les étapes.
    Sauf erreur de ma part , cela ne fonctionne pas comme attendu.
    1/ Le passage du format Date à Heure, écrête directement les valeurs au dessus de 24.
    Dans mon exemple “André” travaille 25h36 en février et 54h43 en mars.
    Donc le total des secondes est faux.
    => Cf IMAGE 1
    Donc la transformation en seconde je l’ai appliquée directement au fichier Excel de départ pour éviter cette étape ( au passage: très simple avec une multiplication par 86400 = conversion jour en secondes )

    2 / Ensuite j’ai créé la mesure, appliqué le bon format et j’ai vérifier les résultats.
    => CF IMAGE 2
    Cela fonctionne correctement, André a bien travaillé 80h19, de plus la somme des heures peut aller jusqu’à 3 Digit car on vois Christophe qui a travaillé 112h17.
    => CF IMAGE 3

    3/ Comme le but est de manipuler le moins possible le fichier Excel de départ mais d’agir par “transformation” BI, j’ai ( juste) changé le format de ma colonne contenant les heures dans Excel en mode : Nombre.
    Puis dans Power Bi, j’ai ajouté une colonne pour réaliser la conversion en secondes ( Nombre *86400)
    J’obtiens donc une colonne contenant bien les heures exprimées en secondes et la Mesure ( créé précédemment fonctionne correctement.
    => CF Image 4

    4/ Mais Alors…. Pourquoi ne pas convertir l’affichage en ” Nombre” directement dans BI ? ( pour ne vraiment pas modifier le fichier Excel de départ )
    Oui mais la date reference (la date 0) d’Excel est le 01/01/1900 et dans BI le 31/12/1899 ( Merci Crosoft ;-)), ce qui ajoute malicieusement lors de la conversion par BI + 1 Jour !
    => Cf Image 5

    Donc je vous propose cette petite alternative pour corriger le tir : un “-1” dans la formule de conversion en secondes.
    De plus si la valeur de départ était “0” BI l’a convertie préalablement en “1”.
    => Cf image 6

    En résumé , les champs Date ou Heure provenant d’Excel sont à transformer ( via BI ) en secondes pour pouvoir les manipuler comme des durées.

    5/ Mes recherches m’ont aussi poussées vers l’utilisation de la notion de durée.
    Je recherche donc un moyen dans Power Query de transfomer une valeur au format #duration(days as number, hours as number, minutes as number, seconds as number) pour utiliser ensuite les fonctions DAX de durée.
    Toutes les tableuqx de bord que je souhaite mettre en place portentn sur des analyses contenant des notions de durée, et ces données sont un peu complexes à manipuler.

    Merci pour votre aide ( et votre lecture car j’ai été bavard) je mets à nouveau en PJ mon fichier de TestHORAIRES2.xlsx.
    Il faudrait également que j’apprenne à intégrer directement les images pour que tous le monde en profite.

    @ bientôt, Philippe.

    Attachments:
    You must be logged in to view attached files.
    #63678
    philippe.moncler
    Participant

    Suite d’image

    Attachments:
    You must be logged in to view attached files.
    #63682
    philippe.moncler
    Participant

    @KIM => Je viens de trouver votre PJ dans le Forum , désolé pour la confusion. 😀

    #63684
    Kim Leblanc
    Participant

    Bonjour Philippe,

    Je vous laisse donc regarder avec mon exemple et revenez-moi si vous avez toujours des questions.

    Kim

    #64689
    Kim Leblanc
    Participant

    Bonjour Philippe,

    Dans le but d’écrire un article sur le sujet, je me suis penchée de nouveau sur votre cas.
    De ce fait, je me suis aperçue que ma solution comportait une erreur.

    Dans les étapes de transformation dans Power Query, lors de la conversion de la date en heure, on perdait la notion de 24 heures. Par exemple dans le cas de Christophe, la transformation de 1900-01-01 21:38:34 se soldait par un 21:38:34 lorsqu’on le transformait en heure alors que le résultat escompté était de 45:38:34 heures, on perdait les 24 heures entre 1899-12-31 et 1900-01-01. Donc modifier le type de données en heures, garde seulement la portion heures de la date.

    Pour contrer ceci, j’ai ajouté une colonne avec l’option dans le menu Date, Date uniquement (pour garde la date seulement sans les heures, minutes, secondes) puis j’ai ajouté une autre colonne pour calculer le nombre de jours avec la fonction M Duration.Days:
    Duration.Days([Date]-#date(1899,12,31))
    puis j’ai multiplié par 24 pour transformer le nombre de jour en heures.

    On pourra alors additionner ces heures aux heures de la portion heure de la date originale. Dans le cas de Christophe: 24 + 21 = 45.

    Le reste de la solution demeure la même. Voir fichier attaché.

    Bonne journée!

    Kim

    Attachments:
    You must be logged in to view attached files.
10 sujets de 1 à 10 (sur un total de 10)
  • Vous devez être connecté pour répondre à ce sujet.