Discussion :

[Tippa]

Bonjour à tous,

Je vous propose un modèle de table de dates en code M, (pouvant donc servir aussi sous Excel Power Query).
Ce modèle inclus de nombreuses colonnes dont le calcul du numéro de semaine ISO (norme européenne ou première semaine de 4 jours).

Pour l'utiliser, sous Power Query :
1/ Créer une nouvelle Requête Vide
2/ Ouvrir l'Editeur avancé
3/ Effacez ce qui s'y trouve, puis Coller le code ci-dessous
4/ Une fois l'éditeur avancé refermé un formulaire apparaît :

Remplir la date à laquelle vous souhaitez commencer votre calendrier (StarDate) et celle de fin (EndDate),
et si besoin d'un calendrier dans une autre langue que celle de votre Power BI, le champ Culture qui est optionnel.
Si vous souhaitez un calendrier en anglais, préciser en dans le champ Culture, ou de pour l'allemand, es pour l'espagnol, zh pour le chinois, ru le russe...
(autres codes langues ici)
N'hésitez pas à donner vos remarques, et, ou, ajout, qui vous sembleraient utiles.
En M ou en DAX pour les colonnes supplémentaires.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
let
CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
//Calcul du nombre de jours du calendrier 
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
//lister les dates pour la durée et créer la table 
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), 
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
//Application du format date à la colonne crée et renommage 
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), 
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "DateRef"}}),
//Année à 4 caractères 
InsertYear = Table.AddColumn(RenamedColumns, "Annee", each Date.Year([DateRef])), ChangeType1=Table.TransformColumnTypes(InsertYear,{{"Annee", Int64.Type}}),
//Trimestre chiffre seul 
InsertQuarter = Table.AddColumn(ChangeType1, "Trimestre", each
Date.QuarterOfYear([DateRef])), ChangeType2=Table.TransformColumnTypes(InsertQuarter,{{"Trimestre", Int64.Type}}),
//N° Mois (de 1 à 12) 
InsertMonth = Table.AddColumn(ChangeType2, "MoisNum", each Date.Month([DateRef])), ChangeType3=Table.TransformColumnTypes(InsertMonth,{{"MoisNum", Int64.Type}}),
//Jour du mois 
InsertDay = Table.AddColumn(ChangeType3, "JourMois", each Date.Day([DateRef])), ChangeType4=Table.TransformColumnTypes(InsertDay,{{"JourMois", Int64.Type}}),
//Date en nombre ISO 
InsertDayInt = Table.AddColumn(ChangeType4, "DateNombreISO", each [Annee] * 10000 + [MoisNum] * 100 + [JourMois]), ChangeType7=Table.TransformColumnTypes(InsertDayInt,{{"DateNombreISO", Text.Type}}),
//Nom du mois 
InsertMonthName = Table.AddColumn(ChangeType7, "MoisNom", each Date.ToText([DateRef], "MMMM", Culture)), ChangeType8=Table.TransformColumnTypes(InsertMonthName,{{"MoisNom", Text.Type}}),
//N° Mois à deux chiffres 
InsertMonthNum2 = Table.AddColumn(ChangeType8, "MoisNum2", each Date.ToText([DateRef], "MM", Culture)), ChangeType9=Table.TransformColumnTypes(InsertMonthNum2,{{"MoisNum2", Text.Type}}),
//Année-Mois 
InsertYearMonth = Table.AddColumn(ChangeType9, "Annee-Mois", each Date.ToText([DateRef], "yyyy-MM", Culture)), ChangeType10=Table.TransformColumnTypes(InsertYearMonth,{{"Annee-Mois", Text.Type}}),
//Nom du mois et année 
InsertCalendarMonth = Table.AddColumn(ChangeType10, "MoisNomAnnee", each (try(Text.Range([MoisNom],0,4)) otherwise [MoisNom]) & " " & Number.ToText([Annee])), ChangeType11=Table.TransformColumnTypes(InsertCalendarMonth,{{"MoisNomAnnee", Text.Type}}),
//Année-Trimestre (préfixé d'un T) 
InsertCalendarQtr = Table.AddColumn(ChangeType11, "Annee-Trimestre", each Number.ToText([Annee]) & " T" & Number.ToText([Trimestre])), ChangeType12=Table.TransformColumnTypes(InsertCalendarQtr,{{"Annee-Trimestre", Text.Type}}),
//Jour de la semaine (numérique) 
InsertDayWeek = Table.AddColumn(ChangeType12, "JourSemaineNum", each
Date.DayOfWeek([DateRef])), ChangeType6=Table.TransformColumnTypes(InsertDayWeek,{{"JourSemaineNum", Int64.Type}}),
//Nom du Jour de la semaine (langue selon culture ou langue de Windows) 
InsertDayName = Table.AddColumn(ChangeType6, "JourSemaineNom", each Date.ToText([DateRef], "dddd", Culture), type text),
//Calcul du jour de fin de semaine 
InsertWeekEnding = Table.AddColumn(InsertDayName, "DateFinSemaine", each Date.EndOfWeek([DateRef]), type date),
//Calcul du jour de fin de mois 
InsertMonthEnding = Table.AddColumn(InsertWeekEnding, "DateFinMois", each Date.EndOfMonth([DateRef]), type date),
//Calcul numéro de semaine ISO (Europe) en 4 étapes 
//1. Determiner la date du jeudi-Thursday de la semaine en cours 
InsertCurrThursday = Table.AddColumn(InsertMonthEnding, "CurrThursday", each Date.AddDays([DateRef], -Date.DayOfWeek([DateRef],1) + 3), type date),
//2. Determiner le 1er janvier de la date (cf étape 1) 
InsertFirstJan = Table.AddColumn(InsertCurrThursday, "FirstJan", each
#date(Date.Year([CurrThursday]),1,1),type date),
//3. Calculer le nombre de jours entre le 1er janvier et le jeudi (cf étape 1) 
InsertDuration= Table.AddColumn(InsertFirstJan, "Duration", each Duration.Days(Duration.From([CurrThursday] - [FirstJan])), type number),
//4. Diviser le nombre de jours calculé à l'étape 3 par 7, arrondir en dessous et ajouter 1 
InsertISOWeekNum = Table.AddColumn(InsertDuration, "NumSemISO", each Number.RoundDown([Duration]/7)+1), ChangeType5=Table.TransformColumnTypes(InsertISOWeekNum,{{"NumSemISO", Int64.Type}}),
//Supprimer les colonnes de calculs inutiles 
RemovedColumns = Table.RemoveColumns(ChangeType5, {"CurrThursday","FirstJan","Duration"})
in 
RemovedColumns 
in 
CreateDateTable

Voir ci-dessous pour le calculs des jours fériés français en DAX...

[Tippa]

Et pour ceux qui en veulent toujours plus, le calcul des jours fériés en DAX cette fois-ci.

*********** COLONNES DAX A CRÉER DANS LA TABLE DE DATES POUR DÉFINIR les JOURS OUVRES ***********
Créer les 5 colonnes suivantes en ajoutant une nouvelle colonne personnalisée pour chaque formule
Dans cet exemple, la table de date se nomme MonCalendrier et la colonne de date [Date], donc à modifier si besoin.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
Formule du calcul de la date du Lundi de Pâques
Date Lun Paques = DATE(1900;1;1)+(ROUND(DATE(MonCalendrier[Année];4;MOD(234-11*MOD(MonCalendrier[Année];19);30))/7;0)*7-6)-1
 
Formule du calcul de la date du Jeudi de l'Ascension
Date Jeu Ascension = MonCalendrier[Date Lun Paques]+38 //Et non 39 jours car décalage d'un jour
 
Formule du calcul de la date du Lundi de Pentecote
Date Lundi Pentecote = MonCalendrier[Date Lun Paques]+49 //Et non 50 jours car décalage d'un jour
 
Formule du calcul de la date du Dimanche de Pentecote
Date Dim Pentecote = MonCalendrier[Date Lun Paques]+48 //Et non 49 jours car décalage d'un jour
 
Formule finale de détermination des jours ouvrés (WE et jours fériés inclus, donc basée sur la création des colonnes ci-dessus)
JoursOuvres = SWITCH (FORMAT (MonCalendrier[Date]; "dd/mm"); 
                        "01/01";0; //Jour de lan
                        "01/05";0; //Fete du travail
                        "08/05";0; //Armistice 39-45                                                
                        "14/07";0; //Fete nationale
                        "15/08";0; //Assomption
                        "01/11";0; //Toussaint
                        "11/11";0; //Armistice 14-18
                        "25/12";0; //Noel
                            SWITCH(VALUE(MonCalendrier[Date]);
                            VALUE(MonCalendrier[Date Lun Paques]);0; //Lundi de paques
                            VALUE(MonCalendrier[Date Jeu Ascension]);0; //Jeudi de l'Ascension
                            VALUE(MonCalendrier[Date Dim Pentecote]);0; //Dimanche de Pentecote
                            VALUE(MonCalendrier[Date Lundi Pentecote]);0; //Lundi de Pentecote
                            SWITCH(MonCalendrier[NomJourSemaine]; //Test du WE (sam dim)
                            "samedi";0;
                            "dimanche";0;1))) //Jour Ferie=0, Non Ferie=1)
 
*********** CALCUL ANNEE FISCALE (juin ou 6) ***********
 
Annee Fiscale(FY) =
  CONCATENATE("FY",
                IF(MONTH(MonCalendrier[Date]) <=6,
                       VALUE(FORMAT('Table'[Date],"YY")),
                       VALUE(FORMAT('Table'[Date],"YY")) +1
                     )
                                )

Espérant avoir fait gagner du temps à certains...

[Braiice]

Merci, ca me servira

[jnauche]

Bonjour Tippa,

Merci pour ce poste. J'ai suivi tes instructions qui m'ont beaucoup servies!!!

Par contre, je rencontre un petit problème lorsque j'utilise la hiérarchie MoisNom à travers cette table... Les mois sont classés par ordre alphabétiques et non par mois calendaires...



Une idée pour reclasser ca?

[Tippa]

Bonjour,

Merci de ton retour, pour les tris, c'est très simple...
Sélectionne la colonne qui n'est pas "bien triée", puis utiliser le bouton Trier par la colonne :



A faire sur toutes les colonnes qui le nécessitent bien sur...

Bonne journée

[jnauche]

Bonjour Tippa,
En utilisant cette méthode, je sélectionne donc ma colonne moisnum et je la trie par la colonne DateNombreIso, j'imagine que cela correspond à la solution que tu proposes...
Mais malheureusement PowerBi ne me laisse pas faire...

Pour moi, il n'y a que le tri par DateNombreIso qui permet d'avoir les mois dans le bon ordre (et les jours/mois également)



Je te laisse me dire ce que je fais mal...

Cordialement,
Julien

[Tippa]

Bonjour,

Pour trier une colonne à l'aide d'une autre, il faut que les deux colonnes aient le même niveau de "détails".
Donc impossible de trier des mois à partir de dates jour par jour.
Donc pour trier du jour mois en texte, il te faut utiliser un jour mois en nombre, etc...

J'espère avoir été clair , bonne journée