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 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 !