Délai entre deux dates, week-end et jours fériés

Proposé par André Meyer-Roussilhon

29 octobre 2021

Restez informé, abonnez-vous à la newsletter (2 fois / mois)

2 + 7 =

Méthode

Précision : je parle ici des jours fériés en France (mais bien sûr, une petite modification du script permet de l’adapter à tout pays).

Le problème des jours fériés, c’est qu’une partie d’entre eux changent chaque année, et dépendent du calcul du jour de Pâques. Précisément, 8 sont fixes, 5 sont mouvants.

La première étape est donc de créer une table calculant automatiquement les jours fériés mouvants, en fonction de la date de début et de la date de fin. Ne cherchez plus, le script est ci-dessous – mais n’oubliez pas de citer votre source !

Cette table, nous allons la créer avec un script M : j’expliquerai dans un prochain article pourquoi cette approche est préférable à celle consistant à créer la table en DAX. Il m’a fallu du temps pour le voir, mais ça ne fait aucun doute !

La deuxième étape, c’est d’utiliser cette date pour créer le calcul, sous forme d’une colonne dans la table des transactions. Oui, une colonne ! Le contexte de ligne, le contexte de filtre, rendent la création de la mesure trop complexe, quand la colonne est elle, très simple.

La table du temps

Commençons par le temps :

Vous êtes dans Power Query. Créez une nouvelle requête vide (Nouvelle source > Requête vide) et collez-y le script ci-dessous, en remplaçant ce qui se trouve déjà dans l’éditeur. Changez si besoin l’année de début (ligne 5). Seul le dimanche est considéré férié (pas le samedi), mais vous pouvez changer ce paramètre à la ligne 48. Le jour de Pâques est calculé ligne 33. Les jours fériés sont déterminés à partir de la ligne 35. Là encore, ils sont valables pour la France, mais vous pouvez adapter.

Le principe est qu’une colonne « Ferie » (pas d’accent, pour simplifier l’écriture du DAX) indique si le jour est travaillé (« ouvré ») ou non (« férié ou dimanche »).

Ajoutez ensuite les autres tables, et lancez la requête.

Voici le script :

let
// Ce script vous est proposé par Daxone.fr
// pour changer la date de début, modifiez le code ci-dessous
// par exemple, un début au 1er janvier 2020 s’écrit #date(2020, 1, 1)
Debut = #date(2019, 1, 1),
Today = DateTime.Date(DateTime.LocalNow()),
Fin = #date(Date.Year(Today), 12, 31),
Length = Duration.Days(Fin-Debut),
// création de la table
Source = List.Dates(Debut, Length, #duration(1, 0, 0, 0)),
EnTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ColRenom = Table.RenameColumns(EnTable,{{"Column1", "Date"}}),
ChangeType = Table.TransformColumnTypes(ColRenom,{{"Date", type date}}),
Perso1 = ChangeType,
AjoutAnnee = Table.AddColumn(Perso1, "Année", each Date.Year([Date]), Int64.Type),
// Année fisc commençant au 1er août
AjoutAnneeFisc = Table.AddColumn(AjoutAnnee , "Année fisc", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
AjoutNomMois = Table.AddColumn(AjoutAnneeFisc , "Nom du mois", each Date.MonthName([Date]), type text),
AjoutNomJour = Table.AddColumn(AjoutNomMois, "Nom du jour", each Date.DayOfWeekName([Date]), type text),
AjoutMoisFisc = Table.AddColumn(AjoutNomJour , "Mois fisc", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6 , Int64.Type),
AjoutNumJour = Table.AddColumn(AjoutMoisFisc, "Num du jour", each Date.Day([Date]), Int64.Type),
// numéro du jour, pour une semaine commençant le lundi
// l’ajout +1 à la fonction DayOfWeek permet au lundi d’être numéroté 1 (au lieu du 0 par défaut)
AjoutNumJourSemaine = Table.AddColumn(AjoutNumJour, "Num du jour Sem", each Date.DayOfWeek(([Date]), Day.Monday)+1, Int64.Type),
AjoutNomJourCourt = Table.AddColumn(AjoutNumJourSemaine, "JJJ", each Text.Start([Nom du jour], 3), type text),
AjoutAAMM = Table.AddColumn(AjoutNomJourCourt , "AAMM", each ([Année]-2000)*100 + [ID Mois]),
AjoutAAMMFisc = Table.AddColumn(AjoutAAMM , "AAMM fisc", each ([Année fisc]-2000)*100 + [Mois fisc]),
ChangeType1 = Table.TransformColumnTypes(AjoutAAMMFisc,{{"AAMM fisc", Int64.Type}}),
AjoutNumMois = Table.AddColumn(ChangeType1, "ID Mois", each Date.Month([Date])),
ChangeType2 = Table.TransformColumnTypes(AjoutNumMois,{{"ID Mois", Int64.Type}}),
OrdreCol = Table.ReorderColumns(ChangeType2,{"Date", "Année", "Nom du mois", "Nom du jour", "JJJ", "Num du jour", "AAMM", "ID Mois", "Année fisc", "Mois fisc", "AAMM fisc"}),
// calcul du jour de Pâques
Paques = Table.AddColumn(OrdreCol, "Paques", each Date.From(Number.Round(Number.From(#date([Année],4,Number.Mod(234-11*
(Number.Mod([Année],19)),30)))/7,0)*7-6)),
// calcul des jours fériés
    JourFerie = Table.AddColumn(Paques, "Ferie", 
each if ([ID Mois]=1 and [Num du jour]=1) then "férié" 
else if ([ID Mois]=5 and [Num du jour]=1) then "férié"
else if ([ID Mois]=5 and [Num du jour]=8) then "férié"
else if ([ID Mois]=7 and [Num du jour]=14) then "férié"
else if ([ID Mois]=8 and [Num du jour]=15) then "férié"
else if ([ID Mois]=11 and [Num du jour]=1) then "férié"
else if ([ID Mois]=11 and [Num du jour]=11) then "férié"
else if ([ID Mois]=12 and [Num du jour]=25) then "férié"
else if ([Date]=[Paques]) then "férié"
else if ([Date]=Date.AddDays([Paques],1)) then "férié"
else if ([Date]=Date.AddDays([Paques],39)) then "férié"
else if ([Date]=Date.AddDays([Paques],49)) then "férié"
else if ([Date]=Date.AddDays([Paques],50)) then "férié"
else "ouvré"),
    SuppPaques = Table.RemoveColumns(JourFerie,{"Paques"})
in
    SuppPaques
Le script en couleur, c’est mieux !

Le délai entre deux dates

Une fois dans Power BI, marquez la table comme table de dates, et reliez-la au reste du modèle :

Enfin créez une colonne dans votre table des transactions avec la formule suivante (courtesy of SQLBI) :

delai livraison = 
CALCULATE(
    COUNTROWS(Datum),
    DATESBETWEEN(
        Datum[Date],
        transactions[date commande],
        transactions[date livraison] -1
    ),
    Datum[Ferie] = "ouvré",
    ALL(transactions)
)

Note : dans cette formule, le «  – 1 » indique que si la commande a eu lieu le lundi et la livraison le mercredi, on compte 2 jours. Vous pouvez modifier ce détail.

Le tour est joué :

N’oubliez pas de citer votre source, je me suis bien cassé la tête pour vous offrir ça, et partagez la page.

A bientôt sur daxone.fr !

Articles associés :

Créer un graphique à base zéro pour suivre l’évolution

Créer un graphique à base zéro pour suivre l’évolution

Dans un graphique à base zéro, le premier point sert de référence (le point zéro), et tous les autres sont calculés en pourcentage de variation par rapport à cette référence. Ce type de graphique est utilisé par exemple dans le domaine financier, pour suivre l’évolution du cours d’une action – mais il peut servir pour suivre l’évolution de tout type de donnée.
Le graphique est intimidant, mais sa réalisation est en fait très facile, et repose sur des formules DAX étonnamment simples.

SUMMARIZECOLUMNS : la super-fonction du DAX

SUMMARIZECOLUMNS : la super-fonction du DAX

Vous ne connaissez peut-être pas cette fonction, et c’est pourtant celle que Power BI utilise le plus fréquemment pour répondre à vos demandes : il suffit de regarder le code généré par Power BI pour afficher votre visuel pour s’en convaincre.
Avec SUMMARIZECOLUMNS , la création d’une table issue de données provenant de différentes autres tables du modèle, incluant de nouveaux indicateurs, et filtrée sur des données précises, devient un jeu d’enfant.

Power Query et M : le nouveau livre de référence

Power Query et M : le nouveau livre de référence

Le quatrième livre que je consacre à Power BI est sorti le 8 septembre : 306 pages entièrement dédiées à Power Query et au langage M, avec de nombreuses mises en pratique.
Plutôt que de séparer l’interface graphique (Power Query) et M (le code que génère l’interface), j’ai choisi de montrer comment les deux doivent être pensés dans une continuité : connaître ne serait-ce qu’un minimum du langage M permet d’améliorer, d’accélérer et d’étendre les possibilités du code généré automatiquement par l’interface graphique.