Suivi à l’identique pour les magasins : gestion de l’année comptable

La solution fonctionne comme prévu pour la plupart. Je l’ai montré à mes pairs et à certains clients.

Les retours ont été positifs, mais j’ai quelques questions et les résultats de ma solution n’étaient pas ceux attendus par la personne qui a posé la question.

Le problème

J’ai découvert un problème lors du calcul de la valeur PY.

Techniquement, les résultats sont corrects, mais ils ne le sont pas du point de vue de l’utilisateur.

Regardez les deux captures d’écran suivantes, qui montrent deux cas différents incluant les mesures Ventes au détail et Ventes au détail PY. Les résultats de ces deux cas peuvent dérouter le public.

Essayez de repérer le problème avant de continuer à lire.

Il s’agit du premier cas pour le magasin de Turin, temporairement fermé entre mars et juillet 2024.

Et voici le deuxième cas pour le magasin Roma, fermé temporairement d’août à octobre 2023 et définitivement fermé en août 2024.

Nous voyons ces résultats pour le deuxième cas :

1. Les valeurs de la mesure des ventes au détail PY pour les magasins « comparables », mais avec une interruption entre août et octobre.
2. Valeurs de la mesure Ventes au détail pour les magasins « Non comparable – Fermeture ».
3. Valeurs de la mesure PY Ventes au détail pour les magasins « Non comparable – Actualiser ».

D’un point de vue technique, ces résultats sont tout à fait logiques et sont corrects.

Les mesures montrent les États L4L corrects pour la période en cours et l’année précédente.

Alors, quels sont les problèmes ?

Pour l’utilisateur, ils sont très déroutants et ne correspondent pas aux attentes.

Pensez-y du point de vue de l’utilisateur :

Lorsque l’on examine les résultats pour des états L4L spécifiques, les deux mesures doivent attribuer les résultats au même état L4L, qu’ils soient calculés pour la période en cours ou pour l’année précédente.

Cela introduit une nouvelle complexité à la solution.

La solution

J’ai besoin d’une deuxième colonne pour la L4LKey de l’année précédente.

Pour la première colonne L4LKey, je compare les dates d’ouverture et de fermeture aux dates mensuelles de l’année précédente (Voir le premier article pour les détails).

Pour la deuxième colonne L4LKey_PY, je dois comparer ces dates aux dates mensuelles de la même année que les dates d’ouverture et de fermeture.

L’idée est quelque peu contre-intuitive, mais elle donne le résultat dont j’ai besoin.
S’il te plaît, reste avec moi, et tu verras comment ça se passe

Tout d’abord, j’ai essayé de le résoudre dans Power Query, comme je l’ai fait dans la solution originale. Mais ça n’a pas marché. J’en viendrai à la raison dans une minute.

Ensuite, je suis passé à la construction de la table Bridge_L4L en SQL, mais les résultats étaient à nouveau inutilisables, car j’avais toujours des lignes dupliquées pour le magasin de Rome, car j’ai deux lignes pour les deux états L4L de ce magasin :

J’ai une ligne chacune pour la fermeture temporaire en 2023 et la fermeture définitive en 2024.

Par conséquent, la jointure renvoie toujours deux lignes, car la clé du magasin est dupliquée.

J’ai donc décidé de passer à une approche procédurale.

Je parcours chaque ligne du tableau contenant les magasins d’ouverture et de fermeture et j’applique les états au tableau, qui comporte une ligne par magasin et par mois.

Je l’ai fait en utilisant des tables temporaires en SQL et le code SQL suivant :

-- Declare all needed variables
DECLARE @StoreKey       int;
DECLARE @OpenDate       date;
DECLARE @CloseDate      date;
DECLARE @L4LKey         int;

-- Create the Cursor to loop through the Stores with each opening, closing, and refresh dates
DECLARE sd CURSOR FOR
    SELECT [StoreKey]
            ,[OpenDate]
            ,[CloseDate]
            ,[L4LKey]
        FROM #tmp_Store_Dates
            -- Order per Closing date, as the procedure must run from the first (oldest) to the last (newest) row
            ORDER BY [CloseDate];

OPEN sd;

-- Get the first row
FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;

-- Start the loop
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Update all rows according to each store based on the L4L status and the respective dates, based on the previous years' dates
    UPDATE [#tmp_Stores_Months]
        SET [OpenDate] = @OpenDate
            ,[CloseDate] = @CloseDate
            ,[L4LKey] = CASE @L4LKey
                            WHEN 2
                                THEN IIF(@OpenDate >= [FirstDayOfMonthPY], @L4LKey, NULL)
                            WHEN 3
                                THEN IIF(@CloseDate <= [LastDayOfMonthPY], @L4LKey, NULL)
                            WHEN 4
                                THEN IIF(@OpenDate >= [FirstDayOfMonthPY] AND @CloseDate <= [LastDayOfMonthPY], @L4LKey, NULL)
                                ELSE 1
                            END
            WHERE [L4LKey] IS NULL
                AND [StoreKey] = @StoreKey;

-- Update based on the same month for the PY calculation
UPDATE [#tmp_Stores_Months]
        SET [OpenDate] = @OpenDate
            ,[CloseDate] = @CloseDate
            ,[L4LKey_PY] = CASE @L4LKey
                            WHEN 2
                                THEN IIF(@OpenDate >= [FirstDayOfMonth], @L4LKey, NULL)
                            WHEN 3
                                THEN IIF(@CloseDate <= [LastDayOfMonth], @L4LKey, NULL)
                            WHEN 4
                                THEN IIF(@OpenDate >= [FirstDayOfMonth] AND @CloseDate <= [LastDayOfMonth], @L4LKey, NULL)
                                ELSE 1
                            END
            WHERE [L4LKey_PY] IS NULL
                AND [StoreKey] = @StoreKey;
    
    -- Get the next row until all rows are processed
    FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;

END

-- Close the Cursor
CLOSE sd;
DEALLOCATE sd;

-- Update the L4LKey and L4LKey_PY in all empty rows
UPDATE #tmp_Stores_Months
    SET [L4LKey] = 1
        WHERE [L4LKey] IS NULL;

UPDATE #tmp_Stores_Months
    SET [L4LKey_PY] = 1
        WHERE [L4LKey_PY] IS NULL;

Le résultat de la procédure est un tableau contenant une colonne mappant les états L4L en fonction de l’année précédente pour chaque mois (L4LKey) et une colonne mappant les états L4L en fonction de la même année pour chaque mois (L4LKey_PY) :

L’étape suivante consiste à importer le résultat de cette procédure dans Power BI et à ajouter une relation supplémentaire entre les tables Bridge_4L et DIM_L4L pour la nouvelle colonne L4LKey_PY :

Cela me permet de contrôler le calcul du résultat PY.

Retail Sales (PY) =
CALCULATE([Retail Sales]
            ,'Time Intelligence'[Time Measures] = "PY"
            ,USERELATIONSHIP('Bridge_L4L'[L4LKey_PY], 'DIM_L4L'[L4LKey])
            )

Désormais, les résultats sont ceux attendus.

Ici, le premier cas :

Et voici les résultats pour le deuxième cas :

Comme vous pouvez le constater, les valeurs PY sont attribuées au même état L4L que les résultats de l’année en cours.

Désormais, l’utilisateur voit des résultats cohérents, beaucoup plus faciles à comprendre.

Conclusion

L’appel supplémentaire du USERELATIONSHIP() La fonction peut être placée dans un élément de calcul et utilisée par toutes les mesures PY.

Cela le rend très facile à utiliser sans aucune logique DAX supplémentaire.

Quoi qu’il en soit, ce défi était relativement facile à résoudre. Mais lorsque j’ai envisagé un calcul mensuel avec la fonctionnalité L4L, j’ai réalisé que cela ne serait pas possible sans du code DAX. Peut-être que j’aborderai ce sujet dans un prochain article.

Mais ce cas souligne la nécessité d’utiliser le point de vue de l’utilisateur lors de la conception et du test d’une solution.

Il ne suffit pas d’utiliser une perspective technique ; le point de vue de l’utilisateur est bien plus important lors de l’évaluation des fonctionnalités et des résultats de la solution.

Pour moi, ce fut une expérience très intéressante et très utile pour mon futur travail.

J’espère que vous trouverez mon approche intéressante. Restez à l’écoute pour ma prochaine pièce.

Références

Voici mon précédent article sur ce sujet :