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 à présenter certains calculs de stocks en DAX.
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
Analyse de la variation d’inventaire pour un seul produit
Modèle de données pour préparer les calculs de stocks en DAX
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.
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.
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 en français ou en anglais. |
|---|
Mesures en DAX pour calculs de stocks
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.
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.
Ce faisant, on peut simplement calculer la balance des mouvements.
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]).
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.
Et pour obtenir la valeur d’ouverture d’un mois, on peut simplement utiliser la fonction OPENINGBALANCEMONTH.
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!
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 :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Formidable la démarche. Merci
Salut, Si je veux faire le suivi des inventaires par jour, comment peut on le faire avec le codage DAX ?
Bonjour Ismail,
Le principe est le même pour le suivi par mois ou par jour. Pour un suivi par jour, il faut alors utiliser la date en ligne dans le tableau croisé dynamique au lieu du mois.
Au plaisir,
Kim