Créer une solution similaire pour les magasins dans Power BI
Qu’est-ce que le like-for-like (L4L)
pour garantir que seuls des éléments comparables sont comparés.
Les éléments peuvent être des produits, des magasins, des groupes de clients, etc.
Ici, vous pouvez lire un bonne explication de ce sujet.
Dans le cas présent, je vais construire une solution pour les magasins.
Les magasins peuvent ouvrir, fermer ou même être temporairement fermés pour des rénovations, des réparations ou pour d’autres raisons.
Les magasins peuvent donc être comparables ou non lorsque l’on compare les résultats actuels avec ceux de l’année précédente. Cela signifie que lorsqu’un magasin n’était pas actif pendant une période spécifique au cours de l’année précédente, il n’est pas comparable dans l’année en cours, où il était actif pendant la même période.
L4L garantira qu’un utilisateur du rapport peut choisir d’inclure ou d’exclure les magasins non comparables.
Pour sélectionner l’état L4L, je crée une table DIM_L4L :
Je peux utiliser les colonnes L4L_Test et Reason comme hiérarchie dans un Slicer ou dans un visuel Matrix.
Les magasins
J’ai choisi quelques magasins dans l’ensemble de données ContosoRetailDW (détails de l’ensemble de données ContosoRetailDW dans la section Références ci-dessous).
Dans ce cas, j’ai choisi les magasins en Italie.
Voici la liste des magasins italiens avec les dates d’ouverture et de fermeture ainsi que les états L4L attribués :
Dans ce tableau, j’ai ajouté deux colonnes avec les dates d’ouverture et de fermeture de fin de mois pour chaque magasin.
Ce tableau contient tous les magasins qui ne sont pas comparables.
Comme vous pouvez le constater, les magasins 224 et 226 ont une date d’ouverture en 2024, le 222 a une date de fermeture en 2024 et les magasins 222 et 225 ont été temporairement fermés en 2023 et 2024.
Tous les autres magasins seront définis comme comparables (L4LKey = 1) lors de la préparation des données pour la solution.
Que surveiller
Alors, quelles sont les exigences ?
- Nous revenons toujours sur l’année précédente. En 2025, nous regardons 2024, et en 2024, nous regardons 2023.
- L’utilisateur doit pouvoir sélectionner chacun des états L4L. Lorsqu’aucun état n’est sélectionné, les données ne sont pas filtrées et tous les magasins sont affichés.
- Nous voulons contrôler les résultats par mois. Il n’est pas nécessaire de modifier les résultats quotidiens.
- Lorsqu’un magasin change d’état de 1 (Comparable) à un autre au cours de l’année précédente, les données doivent être filtrées dans l’année en cours.
Par exemple, un magasin ouvre en août 2024. Si nous examinons uniquement les données comparables de 2025, nous ne devrions voir aucun résultat pour la période de janvier à juillet 2025. - Les mesures utilisées dans les rapports ne doivent pas être modifiées pour refléter les résultats souhaités.
Préparation des données
Tout d’abord, je dois créer un tableau contenant tous les mois. De plus, il doit inclure les première et dernière dates de chaque mois de l’année en cours et des années précédentes.
Pour ce faire, je crée une table comme référence à partir de la table Date dans Power Query.
Je garde uniquement les colonnes suivantes et supprime toutes les autres :
- Clé du mois
- CléMoisPY
- PremierJourDuMois
- DernierJourDuMois
- PremierJourDuMoisPY
- DernierJourDuMoisPY
Après cela, je supprime tous les doublons.
Le tableau L4L_Months ressemble à ceci :
Ensuite, j’ai construit la solution dans Power Query en combinant les tables Store, L4L_Months et la table avec les Stores et les dates d’ouverture et de fermeture (Nom de la table : L4L_Dates).
Construire la solution Power Query
J’ai créé une table référencée à partir de la table « Store » et je l’ai renommée « Bridge_L4L ».
Je supprime toutes les colonnes, à l’exception de la colonne StoreKey.
Ensuite, j’ai besoin d’une ligne pour chaque magasin et chaque mois.
Pour cela, j’ajoute une colonne pour la table L4L_Months :
Lorsque je développe toutes les colonnes de la table L4L_Month, j’obtiens un tableau avec une ligne pour chaque combinaison de magasin et de mois :
Désormais, chaque magasin apparaît plusieurs fois dans la liste. Pour avoir une valeur-clé unique pour chaque magasin, j’ajoute une colonne StoreMonthKey :
Ensuite, je prépare le tableau avec les données du magasin appelé « L4L_Dates ».
Quant à la table Bridge_L4L, j’ai ajouté la table L4L_Months à la table stores, qui contient les dates d’ouverture et de fermeture (Voir Figure 2).
Encore une fois, je développe toutes les colonnes de la table L4L_Months, comme auparavant.
Encore une fois, chaque magasin apparaît plusieurs fois dans la liste. J’ajoute la même valeur-clé unique pour chaque magasin (StoreMonthKey) :
Text.From([StoreKey]) & "_" & Text.From([MonthKey])À ce stade, j’ai toutes les informations nécessaires pour sélectionner les lignes avec le bon état L4L.
Je dois le faire en fonction des dates d’ouverture et de clôture et les comparer aux colonnes First- et LastDateOfMonthPY en utilisant la logique nécessaire par état L4L.
Pour cela, j’ajoute une colonne personnalisée avec l’expression suivante :
if [L4LKey] = 2 and
[OpenDate] >= [FirstDayOfMonthPY]
then true
else if [L4LKey] = 3 and
[CloseDate] <= [LastDayOfMonthPY]
then true
else if [L4LKey] = 4 and ([OpenDate] >= [FirstDayOfMonthPY] and [CloseDate] <= [LastDayOfMonthPY])
then true
else falseJe nomme cette colonne « Valide », car elle marque les lignes correctes pour chaque état L4L.
Ensuite, je filtre les données pour ne conserver que les lignes valides :
L’étape suivante consiste à fusionner la table Bridge_L4L avec la table L4L_Dates en utilisant les colonnes StoreMonthKey précédemment créées :
À ce stade, je n’ai besoin que de la colonne L4LKey de L4L_Dates dans la table Bridge_L4L :
La plupart des lignes contiennent une valeur nulle dans la colonne L4LKey.
Toutes ces lignes concernent les magasins et les mois comparables.
Pour cette raison, je remplace tous les valeurs nulles par 1 :
Enfin, j’ai supprimé toutes les colonnes à l’exception des colonnes nécessaires :
Avec ces étapes, j’ai créé la table Bridge_L4L, qui peut servir de filtre basé sur l’état L4L sélectionné.
Que reste-t-il à faire dans Power BI ?
Maintenant, je dois placer la nouvelle table Bridge_L4L entre les tables Store et la Fact-Table « Retail Sales ».
Ensuite, je peux ajouter une relation du nouveau DIM_L4L à la table Bridge_L4L.
Mais pour ajouter une relation entre la table Bridge_L4L et la table de faits Retail Sales, je dois ajouter la même StoreMonthKey à la table Retail Sales pour identifier de manière unique le magasin pour chaque mois.
Je fais cela dans la requête SQL pour récupérer les données de fait :
SELECT [F].[SaleLineCounter] AS [Sale Line Counter]
,CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey])) AS [DateKey]
,[F].[channelKey]
,[F].[StoreKey]
,CONCAT(CONVERT(nvarchar(25), [F].[StoreKey])
,'_'
,CONVERT(nvarchar(25), YEAR(CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey]))))
,RIGHT('00' + CONVERT(nvarchar(25), MONTH(CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey])))), 2)
) AS [StoreMonthKey]
,[F].[ProductKey]
,[F].[PromotionKey]
,[F].[CurrencyKey]
,[F].[UnitCost]
,[F].[UnitPrice]
,[F].[SalesQuantity]
,[F].[ReturnQuantity]
,[F].[ReturnAmount]
,[F].[DiscountQuantity]
,[F].[DiscountAmount]
,[F].[TotalCost]
,[F].[SalesAmount]
,[F].[DateKeyYear]
FROM [dbo].[v_FactSales] AS [F];Maintenant, j’obtiens cette colonne dans la table de faits :
Après tout cela, le modèle de données pour les tables impliquées est le suivant :
Comme vous pouvez le voir, je n’ai que des relations unidirectionnelles un-à-plusieurs, comme il se doit.
Les résultats
Après avoir ajouté un visuel matriciel au rapport avec la hiérarchie L4L, les magasins et les mois sur les colonnes, j’obtiens ceci pour le montant des ventes pour 2025 :
Regardons les différents scénarios :
- Ouverture des magasins Florence et Milan :
Leurs dates d’ouverture étaient en mai et en octobre 2024. Ces mois ne contenant pas de ventes pour l’ensemble du mois, ils sont considérés comme non comparables. Comme vous pouvez le voir, les ventes basculent entre les états Non comparable – Ouverture et Comparable. - Fermeture du magasin Contoso Roma :
La même photo ici. Le magasin de Rome a fermé ses portes en août 2024. Tout résultat après ce mois est considéré comme comparable. N’oubliez pas qu’il s’agit de données de démonstration et qu’il n’y aura pas de ventes pour novembre et décembre dans le monde réel. Mais des coûts peuvent être affectés au Store si vous souhaitez les analyser, par exemple dans un rapport P&L. - Actualisation du magasin Contoso Torino
Ce magasin a fermé entre mars et juillet 2024. Les ventes de ces mois doivent donc être considérées comme non comparables.
Même en regardant 2024, nous constatons que le magasin de Rome est correctement marqué comme Refresh et que tous les autres magasins sont comparables, à l’exception des magasins de Florence et de Milan :
Les résultats sont exactement ce que j’attendais.
N’oubliez pas que je travaille avec des données de démonstration et que je n’ai intentionnellement pas supprimé les données des magasins fermés. De cette façon, les résultats sont mieux visibles.
Comment faire différemment
Cette approche fonctionne, mais il existe d’autres façons de procéder. Cela dépend des exigences et de l’approche adaptée à votre situation.
- Vous pouvez déplacer cette logique de Power Query vers le langage de programmation de votre choix, tel que SQL ou Python.
- Cette approche, avec la table bridge, est excellente, car elle nous permet de définir la relation entre le magasin et la table Bridge sur un filtrage bidirectionnel et de masquer les magasins qui ne correspondent pas à l’état L4L sélectionné. Toutes les tables de faits sont liées à la table Bridge afin qu’aucune dépendance circulaire ne puisse se produire.
- Une meilleure façon pourrait être d’intégrer l’état L4L dans la ou les tables de faits. Cela éviterait d’avoir recours à la table Bridge en premier lieu.
- Vous pouvez décider d’ajouter une logique d’historisation à la logique de dimension Store et d’y ajouter l’état L4L. Dans ce cas, vous devez inclure la hiérarchie L4L dans la table Store. Cela pourrait être la meilleure approche car elle inclurait une logique SCD2 standard. En même temps, il s’agit d’un choix plus complexe car il ajoute de la complexité lors de la préparation de la table des dimensions du magasin.
Le choix de la meilleure approche de modélisation dépend des exigences et des compétences dont vous disposez.
Conclusion
Aujourd’hui, je vous ai montré comment créer une solution similaire pour comparer les magasins sur plusieurs années.
L’objectif de construire une solution sans modifier les mesures du DAX a été atteint. L’ensemble de la solution est entièrement basé sur les données.
C’est un sujet important. Une logique basée sur DAX peut s’avérer insoutenable, car elle introduit la nécessité d’incorporer une logique DAX supplémentaire dans votre modèle de données. Vous devez toujours y penser lorsque vous ajoutez de nouvelles mesures.
De plus, vous pouvez introduire des problèmes de performances, car le code peut être plus complexe et potentiellement plus lent qu’il ne le serait sans lui.
Je suis un grand fan des solutions basées sur les données. Dans la plupart des cas, ils sont préférables à un code DAX complexe.
J’espère que vous avez appris quelque chose de nouveau et d’intéressant. A bientôt ici.
Références
Ici, une vidéo YouTube de SQLBI sur la création d’une solution L4L pour les marques :
Comme dans mes articles précédents, j’utilise l’exemple de jeu de données Contoso. Vous pouvez télécharger gratuitement l’ensemble de données ContosoRetailDW depuis Microsoft. ici.
Les données Contoso peuvent être utilisées librement sous la licence MIT, comme décrit dans ce document. J’ai mis à jour l’ensemble de données pour déplacer les données vers des dates contemporaines et supprimé toutes les tables non nécessaires pour cet exemple.
You may also like
