Soustraction avec condition

Accueil – Le CFO masqué Forums Power Query Soustraction avec condition

  • Ce sujet contient 6 réponses, 2 participants et a été mis à jour pour la dernière fois par Xav84, le il y a 1 année et 3 mois.
7 sujets de 1 à 7 (sur un total de 7)
  • Auteur
    Articles
  • #125399
    Xav84
    Participant

    Bonjour à toutes et tous,

    Je me tourne vers vous suite à de multiples essais et recherches infructueuses (au vu de ce que propose ChatGPT, ce forum a encore de beaux jours devant lui !)

    Voici mon besoin: je souhaite enrichir un histogramme groupé sur un pbix.
    Axe des X: mes jours.
    Axes des Y: mon niveau de stock de boisson, ainsi que ma consommation de boisson.

    En arrière-plan côté Query, j’ai ceci:

    – une colonne “Qté boisson” en table [Previsionnel boisson], qui indique le nombre de boissons consommées prévu pour aujourd’hui, J+1 et J+2 (ce sont des estimations)
    – une colonne “Total Stock Qty” en table [Stock_boisson] Cette colonne permet d’afficher le niveau de stock de boisson, jusqu’à la veille (car l’inventaire est fait chaque soir, on a donc pas le stock du jour)
    – et enfin un troisième colonne “Date” en table [Date].
    Bien évidemment mes 2 premières tables contiennent des dates, qui sont bien reliées à ma table Date afin que le tout corrobore dans mon histogramme. Nous partons donc de 3 tables distinctes.

    Maintenant que je connais ma consommation de boisson prévue sur les 3 prochains jours, je souhaite rajouter une colonne “Stock prévisionnel” dans ma première table [Previsionnel boisson].
    Cette colonne devra calculer, pour J0, J+1 et J+2 le niveau de stock.

    Pour J0, une soustraction de mon niveau de stock de la veille (connu) par notre estimation de consommation du jour devrait suffire.
    Mais pour J+1 et J+2, comment faire? Car pour J+1 je souhaite repartir du stock de J0 et le soustraire par la consommation prévue sur J+1, et idem pour J+2 je souhaite repartir du niveau de stock de J+1 et le soustraire par la consommation prévue sur J+2.

    Si quelqu’un(e) a un idée de formule (DAX ou M) permettant de répondre à ce besoin.
    Il y a sûrement besoin de rapatrier ma colonne de stock en table [Stock_boisson] vers ma table [Previsionnel boisson] (table.ExpandTableColumn/table.expandNestedJoin ??)

    J’espère avoir été clair. Désolé pour le pavé!
    Merci d’avance pour votre aide.
    Bonne journée !

    #125458
    Xavier Allart
    Participant

    Bonjour
    Sans jeu d’exemple donné, j’espère avoir compris le problème et apporté une réponse satisfaisante.
    Je vous propose en PowerQuery de fusionner les 2 Tables et de calculer les stocks restants à j 0, j 1 et 1 2
    Ce que j’ai réalisé sur la table Conso
    Puis sur la table Conso (2) j’ai réorganisé la structure du tableau en dépivotant les colonnes
    Voir le pbix en pièce jointe En powerquery ouvrir l’editeur avancé pour voir le code commenté.
    Cordialement,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMrDUNzDSNzIwMlbSUQrIL1FILzq8ohjINjEwAJIWYNIISMbqoCl2SkxWOLxAIS0ntbSoWKEgM7WoKBUobArWASFNsOvLS1bISixKycxTKElNzobbYoJbT0hRamZOTmJ6qkJuaVFiDopiiGUYWoIPr0xOLAG6Dep+GAmyKzYWAA==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Articles = _t, #”J 0″ = _t, #”J 1″ = _t, #”J 2″ = _t]),
    #”Type modifié” = Table.TransformColumnTypes(Source,{{“Date”, type date}, {“Articles”, type text}, {“J 0”, Int64.Type}, {“J 1”, Int64.Type}, {“J 2″, Int64.Type}}),
    // Ajout de 3 colonnes de stock en J0, J1, J2
    // Ajouter une colonne personnalisée pour calculer la date du stock)
    #”Personnalisée ajoutée” = Table.AddColumn(#”Type modifié”, “Date stock”, each Date.AddDays([Date],-1)),
    // fusionner les 2 tables sur les champs Articles et Date
    #”Requêtes fusionnées” = Table.NestedJoin(#”Personnalisée ajoutée”, {“Articles”, “Date stock”}, Stock, {“Articles”, “Date”}, “Stock”, JoinKind.LeftOuter),
    // Développer la table pour en extraire le volume du stock
    #”Stock développé” = Table.ExpandTableColumn(#”Requêtes fusionnées”, “Stock”, {“Stock”}, {“Stock”}),
    // Ajouter 3 colonnes personnalisées pour calculer le volume de stock en J0, J1, J2
    #”Personnalisée ajoutée1″ = Table.AddColumn(#”Stock développé”, “Stock 0″, each [Stock] – [J 0]),
    #”Personnalisée ajoutée2″ = Table.AddColumn(#”Personnalisée ajoutée1”, “Stock 1″, each [Stock 0] – [J 1]),
    #”Personnalisée ajoutée3″ = Table.AddColumn(#”Personnalisée ajoutée2”, “Stock 2″, each [Stock 1] – [J 2]),
    #”Type modifié1″ = Table.TransformColumnTypes(#”Personnalisée ajoutée3”,{{“Stock 0”, Int64.Type}, {“Stock 1”, Int64.Type}, {“Stock 2″, Int64.Type}}),
    // Dépivoter le tableau
    // supprimer les colonnes inutiles
    #”Colonnes supprimées” = Table.RemoveColumns(#”Type modifié1″,{“Date stock”, “Stock”}),
    // Sélectionner les colonnes de référence “Date”, “Articles” et dépivoter les autres colonnes
    #”Supprimer le tableau croisé dynamique des autres colonnes” = Table.UnpivotOtherColumns(#”Colonnes supprimées”, {“Date”, “Articles”}, “Attribut”, “Valeur”),
    // Fractionner la colonne “Attribut” en 2 pour avoir une colonne avec Conso ou Stock et une autre avec les jours (0, 1, 2)
    #”Fractionner la colonne par délimiteur” = Table.SplitColumn(#”Supprimer le tableau croisé dynamique des autres colonnes”, “Attribut”, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“Attribut.1”, “Attribut.2″}),
    #”Type modifié2″ = Table.TransformColumnTypes(#”Fractionner la colonne par délimiteur”,{{“Attribut.1”, type text}, {“Attribut.2”, Int64.Type}}),
    // Sélectionner la colonne “Attribut.1” Pivoter la table sur la colonne “Valeur”
    #”Colonne dynamique” = Table.Pivot(#”Type modifié2″, List.Distinct(#”Type modifié2″[Attribut.1]), “Attribut.1”, “Valeur”, List.Sum),
    // Renommer les colonnes
    #”Colonnes renommées” = Table.RenameColumns(#”Colonne dynamique”,{{“J”, “Conso”}, {“Attribut.2”, “Jour”}})
    in
    #”Colonnes renommées”

    Attachments:
    You must be logged in to view attached files.
    #125464
    Xav84
    Participant

    Bonjour Xavier,

    Merci pour ta réponse.
    Je me rends compte que mon message, sans jeu d’exemple donné, est loin d’être clair et compréhensible pour tous.
    Je te joins donc le rapport. J’espère que tu arriveras à y voir plus clair.
    Reviens vers moi pour toute question.

    Bien à toi,
    Excellente soirée.

    • Cette réponse a été modifiée le il y a 1 année et 3 mois par Xav84.
    Attachments:
    You must be logged in to view attached files.
    #125475
    Xavier Allart
    Participant

    Bonjour
    Merci pour les données
    Pour le graphique sont utilisées les tables « Stock_Metal », « Previsonnel metal » et « Dates »
    « Stock_Metal » contient pour chaque article et par date (sur 100 jours calendaires avec ou sans consommation)
    La quantité consommée « Conso_metal.Conso (G)»
    Le stock restant en fin de journée « Total Stock Qty »
    « Previsonnel metal » contient pour chaque article et par date (1 à 4 dates par article)
    Le prévisionnel de consommation « Qté matière »
    Les 2 tables ne contiennent pas les mêmes articles, les calculs de stocks sont donc à réaliser par date seulement
    Les calendriers tiennent compte des week-ends et des jours fériés, pendant lesquels il n’y a pas de consommation. Les jours ne sont donc pas forcément consécutifs du jour au lendemain.

    Je vous propose donc la solution suivante :
    Créer une nouvelle table permettant de connaître le dernier jour de consommation, avec le stock correspondant
    Depuis la table « Stock_Metal » créer une nouvelle table par référence « X_Stock_Metal »
    Cette table permet de connaître le dernier stock
    Filtrer les lignes dont la conso > 0
    Grouper par Date avec les sommes des Stock et de la Conso
    Filtrer pour conserver la date la plus récente
    Depuis la table « Previsionnel metal » créer une nouvelle table par référence « X_ Previsionnel »
    Cette table permet d’identifier les 3 jours suivants la date dernier stock et de calcul le stock restant en fonction des besoins prévisionnels
    Grouper par Date avec les sommes « Qté matière »
    // Rechercher le dernier stock
    Fusionner avec la table « X_Stock_Metal » sur les champs Date
    Développer la colonne pour en extraire les champs « Time – Calendar day » et « Stock »
    Remplir vers le bas les 2 colonnes
    Supprimer les lignes non remplies
    Supprimer la colonne « Time – Calendar day »
    Ajouter un index
    Supprimer la ligne d’index 0
    // Calcul d’un stock temporaire
    Pour J-1 « Stock-Besoin » pour les 2 autres jours « -besoin »
    Supprimer les Stock et Index
    // Calcul du stock restant pour les 3 jours (Tuto Somme Cumulative)
    Ajouter une colonne « Lien » de valeur 1
    Fusionner la requête avec elle-même sur le champ lien
    Développer les colonnes Date et Stock
    Filtrer les dates
    Regrouper par Date et Besoin et calculer le stock

    Pour le calcul de la somme cumulative, je me suis basé sur le tuto suivant :

    Somme cumulative conditionnelle dans Power Query

    En espérant avoir répondu à votre besoin
    Cordialement,

    Attachments:
    You must be logged in to view attached files.
    #125501
    Xav84
    Participant

    Bonjour Xavier,

    Merci pour cette réponse plus que complète qui m’a grandement fait avancé dans ma résolution du problème.

    Néanmoins, il y a un seul petit bémol: le stock duquel nous partons n’est pas exactement le bon.
    En effet, lors de la construction de notre table X_Stock_Metal, lors de la deuxième étape en Query, nous filtrons les lignes pour ne garder que celles dont la conso est <0. Mais cela fait que nous ne prenons pas en compte le stock pour ces lignes concernées, alors notre stock de départ est + bas que la réalité.
    En modifiant cette requête par un simple <=0, j’obtiens bien le bon stock de départ. Mais la date associée à ce stock n’est pas la bonne (il me ressort la date max, alors que je souhaite celle de la veille) et donc j’ai pas mal d’erreurs ensuite dans ma table X_Previsionnel…

    Merci d’avance pour votre aide!

    #125504
    Xav84
    Participant

    Pour + de précisions:

    – en 2ème étape de ma table X_Stock_Metal, le fait de garder uniquement les lignes dont la conso est >0 ne convient pas, car il se peut que nous ayons des références n’ayant pas été consommées la veille, mais nous devons quand même prendre en compte le stock de ces références pour le calcul du stock total.

    – modifier cette requête par un >=0 me fait obtenir le bon niveau de stock, mais la date associée n’est pas la bonne: au lieu que ce stock soit associé à la date de la veille, j’obtiens la date max de ma table. C’est sûrement expliqué par la requête List.Max de l’étape juste après.

    J’ai alors des erreurs à ma table X_previsionnel, qui finit par être vide.

    J’espère avoir été assez clair, merci !

    #125552
    Xav84
    Participant

    Bonjour, 

    Je reviens avec une solution à ma problématique énoncée dans mon dernier message. Pour récupérer mes consos passées en table X_Stock_Metal, au lieu de Filtrer les lignes dont la conso > 0, j’ai simplement filtré de manière à récupérer mes données de la veille (Table.SelectRows(DatesGroup, each Date.IsInPreviousDay([#”date”])), directement après avoir groupé par date la somme des stocks et des consos. Je repars donc bien du stock de la veille pour le calcul des stocks futurs. 

    Néanmoins, j’ai une dernière requête à laquelle je n’arrive pas à répondre. Avec la solution actuelle, je connais uniquement mes consos et stocks futurs TOTAUX (le cumulé des Articles). J’aimerai les connaitre par article. J’ai donc évidemment groupé ma dernière étape en table X_Stock_Metal par Article et par Date, cela fonctionne et les données récupérées sont bien correctes (stock et conso passée propre à chaque article). Mais dans les étapes de ma table X_Previsionnel, je rencontre des difficultés à calculer les stocks et consos prévisionnelles propres à chaque article (avec la somme cumulative utilisée, j’obtiens des stocks négatifs…)
    L’idée finale est de pouvoir filtrer mon graphique par article, et de faire apparaitre les bonnes valeurs pour chaque.

    Je joins un jeu de donnée type et à jour pour une meilleure compréhension de la situation et du sujet.
    Merci pour l’aide !

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