CMUP

  • Ce sujet contient 30 réponses, 3 participants et a été mis à jour pour la dernière fois par pierro1234, le il y a 9 mois.
15 sujets de 16 à 30 (sur un total de 31)
  • Auteur
    Articles
  • #63897
    Sophie Marchand
    Participant

    Bonjour,

    Je vais faire un deal avec vous.

    Vous me fournissez la formule dans Excel qui permet de faire le calcul. Je parle ici d’une formule “unique” pour toute la colonne et non d’une formule qui change à chaque ligne. Si vous me fournissez cette formule dans Excel, je vous transpose le tout dans Power Query. Si c’est impossible à faire dans Excel, j’ai bien l’impression que ce sera impossible dans Power Query aussi.

    Merci.

    Sophie

    #63913
    pierro1234
    Participant

    Bonjour Sophie,
    J’aime votre proposition; je vous avoue qu’elle ma fait beau coup rire!!!!
    Bon je vais essayer de mettre sur une colonne et vous reviens! mais je ne sais pas si je vais y arriver!!!!
    Par contre, la notion de CMUP est très importante a mon avis pour les gestionnaires. J’espère qu’on trouvera une solution.
    Bien à vous,
    Pierre.

    #63916
    pierro1234
    Participant

    Bonjour Sophie,
    Ci-joint un fichier excel “CALCUL-CMUP-V2B.xlsx”.

    Quelques points:
    1/ Il y a une première ligne masquée; je ne sais pas si cela pose un problème
    2/ Il y aura plusieurs articles dans ma base, donc, je suppose qu’apres la source, je rajouterai directement une étape dans la requete pour trier par article et par ordre chronologique

    Voila et merci pour ce que vous pourrez faire.

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

    Bonjour,

    Haha… vous avez déjoué la consigne!

    Dans votre fichier Excel, vous allez lire le résultat d’un calcul d’une ligne précédente et ensuite ce calcul influence d’autres valeurs et ça se répète à chaque ligne. Ce n’est malheureusement pas possible en Power Query, où on travaille avec des bases de données (tables et colonnes). On peut aller chercher des valeurs de lignes précédentes mais pas des calculs qui réfèrent à des lignes différentes à chaque occurence… Si vous arrivez à proposer un fichier Excel en respectant ce concept, je m’engage à mon tour à vous convertir le tout en Excel.

    J’avais moi-même déjà fait un grand bout de chemin, en ramenant les totaux cumulés des lignes précédentes sur les lignes actives (coût total, coût total cumulé, coût total cumulé ligne précédente, quantité achat cumulée, quantité achat cumulée ligne précédente, quantité stock, quantité stock ligne précédente) mais j’ai manqué de jus pour me rendre plus loin.

    À vous de voir si vous y parvenez dans Excel en utilisant les information que j’ai mentionnées ci-dessus, même si dans votre fichier, ces informations sont en “inputs”.

    Au plaisir,

    Sophie

    #63930
    pierro1234
    Participant

    Bonjour Sophie,
    Ce n’est pas simple cette histoire de CMUP!!!! je vais essayer!
    Mais une question si vous me permettez: comment la “Communauté BI” fait avec ce CMUP? Dans la gestion, c’est un élément fondamental et vraiment important pour suivre les marges commerciales.
    La méthode du prix d’achat moyen est à mon avis en tant que gestionnaire, une méthode qui “dilue et nivelle” le cout de revient dans le temps.
    Je vais tenter et vous revient,
    Merci encore,
    Pierre.

    #63974
    Sophie Marchand
    Participant

    Il faut dire que Power BI n’est pas un logiciel de gestion d’inventaire. À priori, il s’agit d’une solution d’intelligence d’affaires, qui repose sur des bases de données. On l’utilise donc davantage pour le reporting que pour ce type de calculs. J’ai fouillé un peu le web, et j’ai trouvé une approche pour calculer en DAX la valeur des stocks selon la méthode FIFO https://radacad.com/dax-inventory-or-stock-valuation-using-fifo. Vous constaterez rapidement que ce n’est pas simple. Peut-être pourrez-vous vous en inspirer pour votre scénario. De mon côté, malheureusement, je manque de temps pour plancher des heures à la résolution de cette problématique. Merci de votre compréhension.

    #63975
    pierro1234
    Participant

    Bonjour Sophie,
    je comprends.
    Je vais voir le site et je continue de chercher sur excel. C’est un plaisir d’échanger avec vous,
    Je vous remercie,
    Pierre.

    #63989
    pierro1234
    Participant

    Rebonjour Sophie,
    Ci-joint la version 4,
    Merci,
    Pierre.

    Attachments:
    You must be logged in to view attached files.
    #63997
    SL
    Participant

    Bonjour

    Comme votre situation nécessite de calculer un élément en fonction du précédent, j’ai pensé à une fonction Power Query un peu complexe : List.Accumulate

    Voici comment je procède :
    – j’ajoute une colonne “Index” pour pouvoir trier dans le même ordre à la fin
    – j’ajoute une colonne “Quantité_signée” pour mettre en négatif les ventes
    – je regroupe par article (car il faut que ça fonctionne avec plusieurs références) je compte le nombre de lignes et je récupère “toutes les lignes” pour avoir une table avec l’ensemble des entrées et des sorties de chacun des articles
    – j’ajoute un “Index2” à cette sous-table afin d’identifier chaque ligne
    – je calcule avec un List.Accumulate la quantité en stock et le montant en stock.
    voir plus bas les explications
    – je ne conserve que les tables et les listes générées à l’étape précédente, je développe les tables et je trie suivant l’index initial. Les listes sont dupliquées sur chacune des lignes
    – je calcul le CUMP en divisant le montant stocké par la quantité en me servant de l’index2 pour récupérer le bon élément de la liste. j’indique que le stock est nul s’il n’y a plus de produit. Votre formule génère un DIV/0 dans ce cas.

    Voici la formule au coeur du calcul : c’est une liste de listes
    chaque “sous-liste” comprenant 2 éléments : le stock en cours et son montant
    le champ [Données] contient les tables avec les achats et les ventes de chacun des articles

    List.Accumulate(
    {1..[Nb_Lignes]-1},
    {{[Données][Quantité_signée]{0},[Données][Quantité_signée]{0}*[Données][#”PA Un”]{0}}},
    (state,current)=>
    List.Combine (
    {state,{{state{current-1}{0}+[Données][Quantité_signée]{current},
    state{current-1}{1}+[Données][Quantité_signée]{current} *
    (if [Données][Quantité_signée]{current}>0
    then [Données][#”PA Un”]{current}
    else state{current-1}{1}/state{current-1}{0})}}})))

    {1..[Nb_Lignes]-1} crée une liste à partir de 1 pour aller chercher les infos dans chaque ligne de la table de l’article (power query commence à 0 et termine à Nb_Lignes -1)

    {{[Données][Quantité_signée]{0},[Données][Quantité_signée]{0}*[Données][#”PA Un”]{0}}}
    défini la première valeur de ma liste : c’est une liste avec la première quantité et le premier montant (quantité *prix)

    (state,current)=>
    List.Combine (
    {state,…

    à partir de la première liste de ma liste, j’agrège les sous-listes les unes derrière les autres

    {{state{current-1}{0}+[Données][Quantité_signée]{current},

    le premier élément de ma nouvelle sous-liste est la quantité en stock : la quantité précédente avec le “current-1″ + la quantité en cours (négative si vente)

    et
    state{current-1}{1}+[Données][Quantité_signée]{current} *
    (if [Données][Quantité_signée]{current}>0
    then [Données][#”PA Un”]{current}
    else state{current-1}{1}/state{current-1}{0})}}

    le second élément de ma sous-liste est le montant : le montant précédent + la quantité en cours multipliée par le prix en cours si achat ou le CMUP précédent si vente

    J’obtiens ainsi une liste pour tous les achats et les ventes de l’article avec à chaque fois le stock et sa valeur résultant de l’entrée ou de la sortie de cet article.

    J’ai dupliqué vos données 100 fois pour avoir 900 lignes, le temps de calcul est de l’ordre de 10 secondes sur mon PC qui n’est pas de première jeunesse !

    J’espère que cela vous sera utile,

    Stéphane

    Attachments:
    You must be logged in to view attached files.
    #64002
    pierro1234
    Participant

    Bonjour Stéphane,
    J’analyse le fichier, j’essaie de comprendre et vous revient,
    je vous remercie,
    Pierre.

    #64009
    pierro1234
    Participant

    Bonjour Stéphane,
    Il me semble que vous avez ajouté des colonnes dans le fichier excel? ou bien je le trompe?
    Les colonnes du fichier excel sont: date, sens, quantité, prix unitaire.
    Pierre.

    • Cette réponse a été modifiée le il y a 9 mois par pierro1234.
    #64012
    SL
    Participant

    Bonjour
    Je suis reparti de votre V4 d’hier.
    Je n’utilise que les colonnes SENS, DATE, QTTE, PA Un et REFART
    C’est tout.
    Les colonnes G à K sont les vôtres.
    Par contre pour tester le fonctionnement du fichier avec plusieurs références, j’ai remplacé A1 par A2 et A3 sur certaines lignes. Mettez A1 partout et vous retrouverez vos données initiales.

    La requête Power Query ajoute une seule colonne : le CUMP.

    Cordialement
    Stéphane

    #64013
    pierro1234
    Participant

    Ok, je comprends!
    En fait, le fichier V4 s’inscrit dans le cadre des échanges avec Sophie.
    Pouvez vous partir de ce fichier V1 ci joint s’il vous plait?
    (la colonne orange est le résultat attendu)
    Je vous remercie.

    Attachments:
    You must be logged in to view attached files.
    #64015
    SL
    Participant

    Rebonjour
    il suffit simplement de supprimer les colonnes G et suivantes pour retrouver votre V1.
    ensuite pour travailler avec PowerQuery il faut mettre sous forme de tableau votre plage (avec comme nom = tableau1).

    d’autre part, en ligne 10 votre REFART est A2 ce qui modifie normalement le calcul. vous ne pouvez vendre l’article A2 si vous ne l’avez pas acheté avant.
    en mettant A1 partout dans mon fichier vous trouverez exactement le résultat que vous attendez

    ce sont des opérations de base d’Excel qu’il faut maîtriser avant d’aller plus loin avec PowerQuery.
    J’ai passé déjà pas loin de 2 heures à réfléchir à votre problème et à écrire un long message très détaillé sur les différentes étapes. Sophie avant moi avait également déjà passé du temps.
    Je vous laisse donc adapter ma solution à votre fichier de travail, je pense qu’il n’y a pas que ces quelques date et une référence
    C’est comme cela qu’on apprend.

    Stéphane

    #64023
    pierro1234
    Participant

    Bonjour Stephane,
    Autant pour moi!vous avez raison.
    C’est juste que quand j’ai ouvert l’éditeur, j’ai vu les colonnes excel du fichier V4.
    Je vous reviens,
    Merci pour votre aide précieuse,
    Pierre.

15 sujets de 16 à 30 (sur un total de 31)
  • Vous devez être connecté pour répondre à ce sujet.