Astuce LuckyTemplates : comment comparer les N premiers jours ouvrables

Pour cet article, j'aimerais parler d'un récent conseil LuckyTemplates que j'ai proposé à un membre du . La question traitait d'un modèle utile en termes d'examen des N premiers jours ouvrables ou des N premiers jours facturables d'un mois donné par rapport à la même période du mois précédent. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

TJ Henneman voulait comparer les 5 premiers jours facturables du mois en cours avec les 5 premiers jours facturables du mois précédent, puis le jour 10, le jour 15 et le jour 20. Notez qu'il souhaite uniquement consulter les jours facturables, cela n'inclut donc pas les week-ends et les jours fériés. Vous pouvez consulter son article .

Je pense qu'il y a une solution de requête de puissance vraiment intéressante à avoir ici. J'ai également travaillé avec une solution DAX intéressante sur ce problème. Je reviendrai sur ce dernier dans un autre tutoriel.

Je souhaite également vous présenter une technique de création de rapports récente que j'ai utilisée pour LuckyTemplates Challenge #16. J'ai utilisé un indicateur KPI défilant qui fonctionnera très bien pour ce problème particulier.

Mais pour cette astuce LuckyTemplates, j'aborderai la solution PowerQuery. Passons à LuckyTemplates et examinons nos données. Pour celui-ci, j'ai simulé les données à l'aide de l'outil d'ensemble de données d'entraînement que nous avons mis en place chez LuckyTemplates, que vous pouvez utiliser gratuitement. C'est idéal pour constituer un ensemble de données de schéma en étoile de base et pour tester des solutions.

Table des matières

• Notre modèle de données
• Création d'une table dupliquée
• Fusionner le tableau des dates
• Création d'une colonne personnalisée
• Création d'un paramètre de simulation
• Création d'une mesure pour le nombre de jours ouvrables
• Conclusion

Notre modèle de données

Notre modèle de données utilise notre table de date étendue plus une série de tables de dimension. Nous avons également une table Sales avec trois années de données partielles la première année, des données complètes la deuxième année et des données partielles la troisième année. Enfin, nous avons également un tableau Mesures .

J'ai aussi légèrement modifié cet ensemble de données. J'ai introduit une table de vacances et l'ai liée à la table de dates étendues afin qu'elle puisse calculer correctement les vacances que nous supprimerons dans cette analyse. Si vous ne savez pas comment procéder, consultez ce tutoriel .

Ensuite, j'ai filtré le premier mois partiel dans l'ensemble de données, car ce mois ne contenait que quelques jours de données et n'avait pas les jours ouvrables dont nous avions besoin. Prenez note que nous commençons avec le premier mois complet; mais à part ça, ce sera votre jeu de données standard. Passons donc à la requête de puissance et commençons à travailler sur cette astuce LuckyTemplates.

Si nous examinons nos données, vous pouvez voir que nous avons différentes commandes un jour donné. L'objectif est de numéroter tout ce qui commence par le 1er avril comme notre premier jour ouvrable, le 2 avril comme notre deuxième jour ouvrable, le 3 avril comme notre troisième jour ouvrable, etc. Nous numéroterons cela de 1 à N jours ouvrables pour chaque mois en excluant les week-ends et les jours fériés.

Création d'une table dupliquée

Pour commencer, nous devons créer un double de ce tableau afin que nous puissions agréger les ventes totales et les intégrer au niveau d'observation individuel. Nous allons appeler cette table dupliquée Sales Aggregated .

Vous avez peut-être remarqué que nous avons dupliqué la table Ventes au lieu de simplement y faire référence. Dans ce cas, nous allons joindre cette table à la table Sales d'origine. Si nous y faisons référence, cela causera des problèmes lorsque nous joindrons les deux.

La première chose à faire est d'utiliser un Group By sur la colonne OrderDate . Cliquez sur le bouton Avancé car cela nous donnera une image complète de ce que nous faisons ici. Nous mettrons Total Day Sales comme nouveau nom de colonne, et ce sera la somme de notre total de ligne.

Nous avons maintenant chaque date (première colonne) et le total des ventes pour ce jour (deuxième colonne).

Fusionner le tableau des dates

La prochaine étape de cette astuce LuckyTemplates consiste à fusionner cela avec notre tableau Dates pour déterminer lesquels de ces jours sont des jours ouvrables et lesquels ne le sont pas. Nous pouvons à nouveau regrouper par , les diviser et les numéroter de manière appropriée.

Nous allons fusionner notre table Dates et lier OrderDate à Date . Nous pouvons voir dans la partie inférieure que la sélection correspond à chaque ligne du tableau, comme il se doit.

Le champ qui nous intéresse principalement est IsBusinessDay .

Il s'agit du champ principal sur lequel nous allons regrouper, puis nous utiliserons les champs Mois et année et MoisnAnnée .

Nous devons également vérifier DayofWeekName pour nous assurer que nous filtrons les bons jours.

Une fois que nous cliquons sur le bouton OK , cela nous donnera exactement ce dont nous avons besoin.

Nous devons également nous assurer que notre date de commande est triée par ordre croissant.

Ensuite, nous revenons en arrière et créons un deuxième Group By .

Cette fois, nous allons cliquer sur le bouton Avancé , grouper sur Mois & Année , puis ajouter un second groupement.

Le deuxième regroupement est pour IsBusinessDay afin que nous puissions numéroter ceux où les jours ouvrables sont vrais.

Ensuite, nous créerons une fonction AllRows afin qu'elle ne s'agrège pas et nous pourrons revenir au niveau de granularité d'origine.

Cela devrait nous donner des tables imbriquées où nous pouvons voir une table pour true et une table pour false pour chaque mois.

Création d'une colonne personnalisée

Ensuite, nous devons créer une colonne personnalisée pour ajouter le nombre dont nous avons besoin pour les jours ouvrables.

Nous allons utiliser une fonction appelée Table.AddIndexColumn , qui fonctionnera sur la table AllRows , et nous l'appellerons Day Index . Nous voulons que l'indice commence par 1 et augmente de 1 à chaque nouveau jour ouvrable.

Ensuite, nous pouvons supprimer les trois premières colonnes et développer notre quatrième colonne personnalisée.

Si nous regardons le résultat, il nous donne l'index des jours, qui compte les jours ouvrables (vrai), puis compte les jours qui sont les week-ends et les jours fériés de chaque mois (faux). Ensuite, nous devons vérifier que ceux-ci sont dans leur type de champ approprié.

Revenons à notre table Sales. Nous allons fusionner la table Aggregate que nous venons de créer avec notre table Sales.

Nous fusionnerons ceux sur OrderDate . Vous pouvez voir en bas qu'il correspond à toutes les lignes du premier tableau.

Lorsque nous développons la colonne Sales Agg, sélectionnez les nouveaux champs que nous venons d'ajouter : les colonnes Month & Year , DayOfWeekName , MonthnYear , IsBusinessDay et DayIndex .

Nettoyons également nos données avant de cliquer sur Fermer et appliquer . Déplaçons la table Sales Agg dans notre section Data Prep , puis déchargeons cette table car nous n'en aurons pas besoin dans le modèle de données. Cliquez ensuite sur Fermer et appliquer .

Notre travail de préparation des requêtes de puissance est maintenant terminé.

Création d'un paramètre de simulation

N'oubliez pas que dans la tâche d'origine, nous voulons faire varier le nombre de jours que nous examinons de 5 à 10 et de 15 à 20. Je pense que la meilleure façon de le faire est d'utiliser un nouveau paramètre what- if .

Nous appellerons le paramètre de simulation First N Business Days et son type de données sera un nombre entier. Le minimum est défini sur un incrément de 1 et un maximum de 20. Ensuite, nous prendrons par défaut 5, qui est la première valeur souhaitée par l'utilisateur.

Nous avons maintenant un segment qui crée automatiquement la mesure de récolte pour capturer la valeur du segment.

Créons un tableau avec nos résultats. Mettons notre dimension Month & Year dans le canevas, puis trions-la par Month & Year .

Une fois que nous avons correctement trié ce champ, nous pouvons simplement supprimer la mesure Total Sales et la développer.

Création d'une mesure pour le nombre de jours ouvrables

Nous devons créer une autre mesure qui examine la valeur du paramètre du curseur et ne calcule que le nombre de jours ouvrables.

Nous appellerons cette nouvelle mesure Total Sales N Bus Days . Nous allons commencer par la fonction puisque nous changeons définitivement de contexte, et par notre mesure Total Sales. Nous allons filtrer cela avec le de notre table Sales.

L'étape suivante consiste à écrire les conditions que nous voulons prendre en compte pour le curseur. Nous avons notre Sales Day Index que nous avons créé dans Power Query, et nous voulons qu'il soit inférieur ou égal à la valeur First N Business Days, qui est la valeur récoltée du slicer.

Par exemple, si le curseur est à 5, nous voulons tous les jours de la table Ventes où l'indice de jour est inférieur ou égal à 5.

Et puis nous voulons supprimer les jours non ouvrables. Nous allons référencer la table Date et utiliser le champ IsBusinessDay. Nous ne regarderons que le résultat qui est VRAI.

Enfin, ramenez le contexte pour le mois et l'année et fermez la mesure. Nous devrions être prêts à partir à ce stade.

Déposons la nouvelle mesure dans notre tableau.

Nous pouvons amener le trancheur de curseur à la marque des 10 jours, et la table calculera dynamiquement à la marque des 10 jours.

Conclusion

Dans cette astuce LuckyTemplates, nous avons expliqué comment effectuer une comparaison similaire de manière dynamique à l'aide d'une solution Power Query. Nous pouvons mettre cela dans un graphique à barres ou un graphique linéaire.

Dans certains de nos prochains didacticiels, nous examinerons la solution DAX et implémenterons la visualisation du défileur KPI.

Si vous avez apprécié le contenu couvert dans ce didacticiel particulier, n'oubliez pas de vous abonner à la chaîne de télévision LuckyTemplates .