Sommaire
En général, les calendriers commencent le 1er janvier. Mais cette règle générale ne s'applique pas toujours en entreprises, pour lesquelles le calendrier fiscal se substitue au calendrier civil pour l'essentiel des analyses. Comment gérer ce parallaxe dans vos outils de pilotage en customisant votre table de dates Power Query, c'est ce que nous allons voir ensemble dans cet article.
Dans l'univers de la data et de la BI (Business Intelligence), la table de dates est un élément essentiel de toute analyse un peu avancée. Notamment lorsqu'il s'agit d'analyses temporelles, comme le chiffre d'affaires cumulé sur une année ou la variation du résultat par rapport à l'année précédente.
Dans Excel et Power BI, tous les outils et les fonctions DAX de Time Intelligence permettent de manipuler facilement la dimension temporelle de notre data pour créer les indicateurs et les analyses nécessaires et obtenir des analyses temporelles pertinentes. Mais tout ceci prend par défaut en compte un calendrier civil normal (du 1er janvier au 31 décembre).
Comment faire alors pour adapter tout ces chiffres à un calendrier décalé, qu'il soit fiscal, scolaire ou autre, sans commencer à tout bricoler manuellement ?
Générer une table de dates "normale" via Power Query
Dans cet article, nous partons d'une table de dates complète déjà créée avec Power Query, à laquelle nous allons ajouter les colonnes correspondant aux périodes fiscales de référence (année, trimestre, mois) sans rien enlever ni modifier des colonnes initiales. Nous décalerons ensuite la plage de dates du calendrier pour commencer et finir sur les dates du calendrier fiscal, et non sur les dates de début et de fin du calendrier civil.
Vous pouvez retrouver l'article complet sur la création de table de dates ici ou simplement récupérer le code suivant à intégrer dans une requête vide Power Query (que vous utilisiez Excel ou Power BI) :
let
DateDebut = #date(Date.Year( List.Min( Requête1[Date] ) ), 1, 1), //Vous pouvez indiquer la requête et la colonne à utiliser pour calculer cette date
DateFin = #date(Date.Year( List.Max( Requête1[Date] ) ), 12, 31), //Vous pouvez indiquer la requête et la colonne à utiliser pour calculer cette date
NombreDeJours = Number.From(DateFin - DateDebut) + 1,
ListeDates = List.Dates(DateDebut, NombreDeJours, #duration(1, 0, 0, 0)),
TableDesDates = Table.FromList(ListeDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenommerDate = Table.RenameColumns(TableDesDates,{{"Column1", "Date"}}),
TypeDate = Table.TransformColumnTypes(RenommerDate,{{"Date", type date}}),
AjouterAnnee = Table.AddColumn(TypeDate, "Année", each Date.Year([Date]), Int64.Type),
AjouterMois = Table.AddColumn(AjouterAnnee, "Mois", each Date.Month([Date]), Int64.Type),
AjouterNomMois = Table.AddColumn(AjouterMois, "Nom du mois", each Date.MonthName([Date]), type text),
AjouterCleTriMois = Table.AddColumn(AjouterNomMois, "Clé tri mois", each Date.ToText([Date], "yyyy-MM"), type text),
AjouterTrimestre = Table.AddColumn(AjouterCleTriMois, "Trimestre", each "T" & Text.From(Date.QuarterOfYear([Date])), type text),
AjouterSemaine = Table.AddColumn(AjouterTrimestre, "Semaine de l'année", each Text.From([Année]) & "-" & Text.PadStart(Text.From(Date.WeekOfYear([Date])), 2, "0"), type text),
AjouterJour = Table.AddColumn(AjouterSemaine, "Jour du mois", each Date.Day([Date]), Int64.Type),
AjouterJourSem = Table.AddColumn(AjouterJour, "JourSem", each Date.DayOfWeek([Date]) + 1, Int64.Type),
AjouterNomJour = Table.AddColumn(AjouterJourSem, "Nom du jour", each Date.DayOfWeekName([Date]), type text)
in
AjouterNomJour
Copiez ce code, ouvrez une requête vierge dans votre éditeur Power Query, cliquez sur l'éditeur avancé puis collez le code de la requête :
Dans tous les cas, ce code, une fois inséré dans une requête vide, doit être modifié pour s'appuyer sur les dates d'une autre de vos tables (les ventes, la compta, les absences...). Vous devez donc modifier dans les deux lignes du haut (DateDebut et DateFin) pour indiquer à chaque fois la requête et la colonnes adéquates.
Indiquer le numéro du premier mois de l'année fiscale
Une fois le calendrier normal créé, la première étape consiste à indiquer dans une variable le numéro du premier mois de l'année fiscale.
Variabiliser cette information - au tout début de la requête - permettra de facilement modifier sa valeur pour une entreprise ou une filière différente par exemple. En retrouvant facilement l'emplacement de l'information initialement saisie, et en la modifiant à cet unique emplacement.
Dans tous les exemples formules et images qui illustreront cet article, nous utiliserons un calendrier fiscal commençant en juillet, le 7ème mois de l'année civil.
La variable à rajouter au début de la requête :
NumPremierMoisFiscal = 7
Ajouter les colonnes de périodes fiscales
Une fois l'information du numéro du premier mois de l'année intégrée à la requête, il faut créer les colonnes qui seront les dimensions temporelles utiles de nos analyses futures. Et notamment le mois fiscal, le trimestre fiscal et l'année fiscale.
La colonne du mois fiscal
C'est la première colonne à créer, et c'est aussi celle dont la formule est la plus complexe : celle du mois fiscal
Le principe est celui-ci :
Le premier mois de l'année fiscale est le mois numéro 1, et les mois augmentent de 1 en 1 jusqu'au 12ème mois de l'année fiscale.
Nous allons donc à la fois utiliser des additions et des soustractions, mais aussi une fonction Number.Mod (modulo) pour ne jamais dépasser le nombre 12.
Le code de la colonne à ajouter dans la requête :
Number.Mod( [Mois] - NumPremierMoisFiscal + 12 , 12 ) + 1
Résultats (avec NumPremierMoisFiscal = 7) :
- Juillet = 1 (premier mois de l'année fiscale)
- Septembre = 2
... - Décembre = 6
- Janvier = 7
... - Mai = 11
- Juin = 12
La colonne du trimestre fiscal
C'est la deuxième des 3 colonnes à créer. Plutôt que d'utiliser une logique conditionnelle pour chaque trimestre en fonction du numéro du mois fiscale, nous optons pour une approche mathématique.
Le principe est le suivant :
Chaque numéro de mois est divisé par 3 et le résultat est arrondi à l'entier supérieur.
Le code de la colonne à ajouter dans la requête :
"T" & Text.From( Number.RoundUp( [MoisFiscal] / 3 , 0 ) )
Résultats (avec NumPremierMoisFiscal = 7) :
- Juillet = T1 (premier trimestre de l'année fiscale)
- Septembre = T1
... - Décembre = T2
- Janvier = T3
... - Mai = T4
- Juin = T4
La colonne de l'année fiscale
Le principe à appliquer pour l'année fiscale est celui-ci :
Si le mois de la date analysée est supérieur ou égal au premier mois de l'année fiscale, l'année fiscale correspond à l'année de la date analysée.
Sinon, l'année fiscale est celle qui précède l'année de la date analysée.
Le code de la colonne à ajouter dans la requête :
[Année] + ( if [Mois] < NumPremierMoisFiscal then -1 else 0 )
Résultats (avec NumPremierMoisFiscal = 7) :
- Mai 2026 = 2025
- Juin 2026 = 2025 (denier mois de l'année fiscale 2025)
- Juillet 2026 = 2026 (premier mois de l'année fiscale 2026)
- Août 2026 = 2026
Redimensionner la plage de dates
C'est sans doute l'étape la plus complexe de cette table de date décalée : modifier les dates de début et de fin de la table de dates pour qu'elles correspondent à des exerices fiscaux complets
Déterminer les dates min et max
Pour commencer et pour se simplifier les étapes suivantes, il est plus simple de récupérer dans 2 variables distinctes la date la plus ancienne à utiliser et la date la plus récente à utiliser. Suivant les cas, ces 2 dates peuvent bien entendu venir de 2 requêtes ou colonnes différentes. Il est bien sûr possible de déterminer encore plus précisément ces 2 dates en ajoutant d'autres sources de dates.
Etapes à ajouter dans la requête :
DateMin = List.Min( VENTES[Date] ) ,
DateMax = List.Max( VENTES[Date] ) ,
Déterminer la date de début de la requête
Contrairement à un calendrier normal, là nous devons effectuer une double vérification :
- DateDebut1 : Quelle est la date de début de l'année fiscale de mon année la moins récente ?
- DateDebut2 : Est-ce que la date de mon DateMin est avant le début de cette année fiscale ?
Etapes à ajouter dans la requête :
DateDebut1 = #date( Date.Year( DateMin ), NumPremierMoisFiscal , 1),
DateDebut2 = if DateMin >= DateDebut1 then DateDebut1 else Date.AddYears( DateDebut1 , -1 ) ,
Déterminer la date de fin de la requête
De la même manière que pour la date de début, il faut aussi effectuer une double vérification :
- DateFin1 : Quelle est la date de fin de l'année fiscale de mon année la plus récente ?
- DateFin2 : Est-ce que la date de mon DateMax est près la fin de cette année fiscale ?
Etapes à ajouter dans la requête :
DateFin1 = Date.AddDays( #date(Date.Year( DateMax ), NumPremierMoisFiscal , 1) , -1 ),
DateFin2 = if DateMax <= DateFin1 then DateFin1 else Date.EndOfMonth( Date.AddYears( DateFin1 , 1 ) ) ,
Générer l'ensemble des dates de la requête
Une fois les dates limites de début et de fin déterminées, il faut générer la liste de toutes les dates du calendrier.
Comme d'habitude dans cette requête, pour plus de clareté, nous avons segmenté le résultat en 2 étapes : 1/ Déterminer la durée de la période, 2/ Générer les dates
Etapes à ajouter dans la requête :
NombreDeJours = Number.From(DateFin2 - DateDebut2) + 1,
ListeDates = List.Dates(DateDebut2, NombreDeJours, #duration(1, 0, 0, 0)),
La requête Power Query complète à copier-coller
Evidemment, chez Solpedinn, notre passion c'est de vous permettre d'apprendre afin d'être autonomes. C'est pour ça que tenons à expliquer en détails quoi faire et comment le faire. Mais une fois que vous savez, le plus simple est quand même de copier-coller une requête 100% prête à l'emploi.
Et c'est exactement ce que vous trouverez juste après : la requête complète à copier-coller pour obtenir une table de date qui prenne en compte le calendrier fiscal de votre organisation
Voici la requête à copier-coller dans une requête vide :
let
NumPremierMoisFiscal = 7 , //Vous pouvez indiquer le numéro du premier mois de l'année fiscale
DateMin = List.Min( Requête1[Date] ) , //Vous pouvez indiquer la requête et la colonne à utiliser pour calculer cette date
DateMax = List.Max( Requête1[Date] ) , //Vous pouvez indiquer la requête et la colonne à utiliser pour calculer cette date
DateDebut1 = #date( Date.Year( DateMin ), NumPremierMoisFiscal , 1),
DateDebut2 = if DateMin >= DateDebut1 then DateDebut1 else Date.AddYears( DateDebut1 , -1 ) ,
DateFin1 = Date.AddDays( #date(Date.Year( DateMax ), NumPremierMoisFiscal , 1) , -1 ),
DateFin2 = if DateMax <= DateFin1 then DateFin1 else Date.EndOfMonth( Date.AddYears( DateFin1 , 1 ) ) ,
NombreDeJours = Number.From(DateFin2 - DateDebut2) + 1,
ListeDates = List.Dates(DateDebut2, NombreDeJours, #duration(1, 0, 0, 0)),
TableDesDates = Table.FromList(ListeDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenommerDate = Table.RenameColumns(TableDesDates,{{"Column1", "Date"}}),
TypeDate = Table.TransformColumnTypes(RenommerDate,{{"Date", type date}}),
AjouterAnnee = Table.AddColumn(TypeDate, "Année", each Date.Year([Date]), Int64.Type),
AjouterMois = Table.AddColumn(AjouterAnnee, "Mois", each Date.Month([Date]), Int64.Type),
AjouterNomMois = Table.AddColumn(AjouterMois, "Nom du mois", each Date.MonthName([Date]), type text),
AjouterCleTriMois = Table.AddColumn(AjouterNomMois, "Clé tri mois", each Date.ToText([Date], "yyyy-MM"), type text),
AjouterTrimestre = Table.AddColumn(AjouterCleTriMois, "Trimestre", each "T" & Text.From(Date.QuarterOfYear([Date])), type text),
AjouterSemaine = Table.AddColumn(AjouterTrimestre, "Semaine de l'année", each Text.From([Année]) & "-" & Text.PadStart(Text.From(Date.WeekOfYear([Date])), 2, "0"), type text),
AjouterJour = Table.AddColumn(AjouterSemaine, "Jour du mois", each Date.Day([Date]), Int64.Type),
AjouterJourSem = Table.AddColumn(AjouterJour, "JourSem", each Date.DayOfWeek([Date]) + 1, Int64.Type),
AjouterNomJour = Table.AddColumn(AjouterJourSem, "Nom du jour", each Date.DayOfWeekName([Date]), type text),
#"Aj col MoisFiscal" = Table.AddColumn(AjouterNomJour, "MoisFiscal", each Number.Mod( [Mois] - NumPremierMoisFiscal + 12 , 12 ) + 1 , Int64.Type ),
#"Aj col TrimestreFiscal" = Table.AddColumn(#"Aj col MoisFiscal", "TrimestreFiscal", each "T" & Text.From( Number.RoundUp( [MoisFiscal] / 3 , 0 ) ) , type text ),
#"Aj col AnnéeFiscale" = Table.AddColumn(#"Aj col TrimestreFiscal", "AnnéeFiscale", each [Année] + ( if [Mois] < NumPremierMoisFiscal then -1 else 0 ) , Int64.Type )
in
#"Aj col AnnéeFiscale"
N'hésitez pas à mettre le lien de cet article en favoris pour retrouver facilement le code complet pour vos prochains dashboards Excel ou Power BI.
Vous voulez aller plus loin et être accompagné dans une formation individuelle sur-mesure Excel ou Power BI directement appliquée sur vos données ? Alors rdv sur notre site et sur nos formations Excel et Power BI éligibles au CPF. Vous voulez former vos équipes ou demander à votre entreprise de choisir notre organisme pour vos formations, alors rdv sur notre formulaire de contact.
Dans tous les cas, à bientôt dans une approche moderne et avancée d'Excel, Power Query et Power BI.
