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

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

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

calculs de stocks en DAX

 

Analyse de la variation d’inventaire pour un seul produit

calculs de stocks en DAX

 

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.

 

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.

Formations Power BI disponibles 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.

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 :

Le-CFO-masque_Commentaires-Introduction-langage-DAX
CFO-Masque_Formations-en-ligne_FBLa 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é.

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 *

Retour en haut