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

Publié le 01 octobre 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Variation d'inventaire

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

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
 
 

Vous devez analyser de grandes quantités de données et les présenter dans des rapports et tableaux de bord, avec des indicateurs de performance pertinents ? Développez vos compétences avec nos formations en Power BI.

 

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

 


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 (Data Analysis Expression), utilisé par Power Pivot et par Power BI Desktop, suivez la formation Introduction au langage DAX (Power BI et Power Pivot) qui vous permettra 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.
 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Edith Lemay
Écrit il y a 2 mois
Explications claires et bien structurées.

Super intéressant! Les exercices pratiques aide beaucoup à assimiler la matière.

Nicolas Grandclaude
Écrit il y a 3 mois
Je recommande vivement cette formation.

Une formation indispensable pour la mise en place d'un modèle de données automatique et performant.

David Bansard
Écrit il y a 4 mois
De nombreuses portes se sont ouvertes

Etant, moi-même, dans le métier de la formation, j'avais besoin d'un apprentissage beaucoup plus pertinent et professionnel que se débrouiller tout seul chez soi. De nombreuses portes se sont ouvertes sur la manière de comprendre l'outil Power BI et donc de correctement l'utiliser.

Remi Martinato
Écrit il y a 4 mois
Superbe formation même si le langage Dax est très complexe à comprendre.

Cette formation clôture mon parcours « Excel tableaux de bord » qui a été pour moi extrêmement enrichissant. Cela m a permis de découvrir des fonctions d Excel, de prendre plaisir à les utiliser et de développer ma créativité afin de produire des tableaux de bord encore plus percutants. La formatrice maîtrise sur le bout des ongles les différents outils.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

1 réflexion sur “DAX: Calculer un stock d’ouverture et un stock de fermeture”

Laisser un commentaire

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

Scroll to Top