Contact: 514-605-7112 / info@lecfomasque.com

DAX: Calculer un stock d’ouverture et un stock de fermeture

Publié le : 1 octobre 2019

Récemment, un usager de Power Pivot, dans Excel, a posé une question sur le forum concernant l’analyse d’inventaire. Il cherchait à connaître les bonnes formules DAX pour présenter ses stocks d’ouverture et de fermeture. Cet article vise à résoudre le cas de cet usager.

 

Analyser les mouvements d’inventaire

L’idée est d’en arriver à créer un tableau croisé dynamique qui permette de suivre les inventaires par mois et par produit, tel qu’illustré ci-dessous.

 

Analyse de la variation d’inventaire pour tous les produits

Données départ inventaire

 

Analyse de la variation d’inventaire pour un seul produit

Deuxième exemple

 

Modèle de données

Si vous ne le savez pas encore, afin de pouvoir créer de bonnes formules en DAX, il vous faut un modèle de données BÉTON! Dans notre exemple, nous avons 4 tables. Au départ, l’usager du forum utilisait un modèle qui à mon avis, ne respectait pas les meilleures pratiques d’affaires. Pourquoi? Et bien, les tables n’avaient pas bien été identifiées comme tables de faits et tables de dimensions. Plus précisément, parmi ces 4 tables, on a 2 tables de faits, soit la table T_SI (comprend les stocks d’ouverture) et la table T_Mouv (comprend les mouvements d’inventaire) et on a 2 tables de dimensions T_ListeArt (dimension de produits) et T_Date (dimension de dates). Ce faisant, les relations sont toutes des relations plusieurs à un en partance des tables de faits. À noter que la table de stocks d’ouverture ne comprenait pas de colonne de dates à l’origine. J’ai corrigé le tout en ajoutant la date de début d’année, dans notre exemple, le 2019-01-01. C’est ce qui m’a permis de créer une relation avec la table de dates.

 

Modèle données inventaire

 

Vous verrez plus tard, dans cet article, que je crée des fonctions de TIME INTELLIGENCE. Avec la table de dates fournie dans le modèle de l’usager, j’étais incapable de créer de telles mesures. Power Pivot renvoyait un message mentionnant que les dates ne se suivaient pas toutes. J’ai donc recréé de toutes pièces une table de dates, en utilisant la fonctionnalité à cet effet dans Power Pivot.

 

Table date calendrier

 

Mesures en DAX

Je vous présente ci-dessous la liste des mesures utilisées dans mon modèle de données et je vous les explique ensuite à l’aide d’un tableau croisé dynamique.

 

La mesure suivante sert à déterminer le stock de début d’année (avant les mouvements d’inventaires). J’ai utilisé la fonction ALL car je souhaite que ce stock soit additionné a l’ensemble des mouvements de stocks au fil du temps, pour atteindre le stock de fermeture. La fonction ALL permet d’ignorer tous les filtres de dates apposés sur la table.

Mesure quantités

 

Les mesures suivantes servent simplement à calculer les entrées et les sorties d’inventaires, qui sont identifiées par IN et OUT dans une des colonnes de la table de mouvement d’inventaires.

Mesure Entrées
Mesure Sorties

 

Ce faisant, on peut simplement calculer la balance des mouvements.

Mesure Balance Mouv

 

Si on veut, à chaque mois, récupérer l’ensemble des entrées et des sorties d’inventaires des mois précédents, on peut utiliser une fonction CALCULATE avec un FILTER(ALL) sur la table de dates, et poser une condition pour ne retenir que les mouvements survenus pendant le mois en cours et pendant les mois précédents (<=MAX(T_Date[Date]).

Mesure Mouv Act

 

On pourra ainsi obtenir le solde d’inventaire de fermeture en additionnant le montant de stock en début d’année aux mouvements cumulatifs de stock.

Mesure Stock final

 

Et pour obtenir la valeur d’ouverture d’un mois, on peut simplement utiliser la fonction OPENINGBALANCEMONTH.

Mesure stock ouverture

 

Dans le tableau croisé dynamique ci-dessous, que j’ai utilisé simplement pour vous montrer le comportement de mes mesures, on voit que la Qté de stock initiale demeure à 33 pour tous les mois. On voit aussi que les entrées et les sorties de stocks se calculent comme prévues. Donc, en janvier, le stock final est le résultat de 33 + 32 – 7 = 58 ou encore 33 + 25 = 58. En février, le stock final est de 33 + 32 + 7 + 22 + 6 = 74 ou encore 33 + 41 = 74 ou encore 58 + 22 – 6 =74. Et finalement, on voit bien que le stock initial se trouve à être le stock final du mois précédent, merci à la fonction OPENINGBALANCEMONTH!

 

Explication du langage DAX

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse McDo ,

    Formidable la démarche. Merci

    Laisser un commentaire