Tout savoir sur Power BI

Calendrier DAX

Code court
DAX
Calendrier = 
VAR
Base = YEAR(MINX(CALENDARAUTO(), [Date]))
RETURN
ADDCOLUMNS(
    CALENDARAUTO(),
    "@Année",          FORMAT([Date], "YYYY"),
    "Rang Année",      YEAR([Date]) - Base + 1,
    "@Semestre",       IF(MONTH([Date]) <= 6, "S1", "S2"),
    "Année Semestre",  YEAR([Date]) & "-" & IF(MONTH([Date]) <= 6, "S1", "S2"),
    "Rang Semestre",   (YEAR([Date]) - Base) * 2 + IF(MONTH([Date]) <= 6, 1, 2),
    "@Trimestre",      FORMAT([Date], "TQ"),
    "Année Trimestre", FORMAT([Date], "YYYY-TQ"),
    "Rang Trimestre",  (YEAR([Date]) - Base) * 4 + QUARTER([Date]),
    "@Mois",           FORMAT([Date], "MM"),
    "Année Mois",      FORMAT([Date], "YYYY-MM"),
    "Rang Mois",       (YEAR([Date]) - Base) * 12 + MONTH([Date]),
    "@Semaine",        FORMAT(WEEKNUM([Date], 2), "00"),
    "Année Semaine",   YEAR([Date]) & "-" & FORMAT(WEEKNUM([Date], 2), "00"),
    "Rang Semaine",    (YEAR([Date]) - Base) * 52 + WEEKNUM([Date], 2)
)
Code long
DAX
Calendrier = 
VAR 
Base = YEAR(MINX(CALENDARAUTO(), [Date]))
RETURN
ADDCOLUMNS(
    CALENDARAUTO(),
    "@Année",          FORMAT([Date], "YYYY"),
    "Rang Année",      YEAR([Date]) - Base + 1,
    "@Semestre",       IF(MONTH([Date]) <= 6, "S1", "S2"),
    "Année Semestre",  YEAR([Date]) & "-" & IF(MONTH([Date]) <= 6, "S1", "S2"),
    "Rang Semestre",   (YEAR([Date]) - Base) * 2 + IF(MONTH([Date]) <= 6, 1, 2),
    "@Trimestre",      FORMAT([Date], "TQ"),
    "Année Trimestre", FORMAT([Date], "YYYY-TQ"),
    "Rang Trimestre",  (YEAR([Date]) - Base) * 4 + QUARTER([Date]),
    "@Mois",           FORMAT([Date], "MM"),
    "@Mois Lettre" ,   FORMAT([Date],"MMMM"),
    "Année Mois",      FORMAT([Date], "YYYY-MM"),
    "Rang Mois",       (YEAR([Date]) - Base) * 12 + MONTH([Date]),
    "@Semaine",        FORMAT(WEEKNUM([Date], 2), "00"),
    "Année Semaine",   YEAR([Date]) & "-" & FORMAT(WEEKNUM([Date], 2), "00"),
    "Rang Semaine",    (YEAR([Date]) - Base) * 52 + WEEKNUM([Date], 2),
    "@Jour Mois" ,     FORMAT([Date],"DD"),
    "@Jour Semaine" ,  WEEKDAY([Date], 2),
    "@Jour Lettre" ,  FORMAT([Date],"DDDD"),
    "Année Jour" ,    FORMAT([Date],"YYYY-MM-DD"),
    "Rang Jour" ,     INT([Date] - DATE(Base, 1, 1)) + 1
)

Suivant vos besoins, vous pouvez copier/coller un de ces codes tel quel après avoir créé une Nouvelle table à partir de l’onglet Outils de table. La colonne Date de cette table Calendrier pourra être reliée dans votre projet à toutes les tables existantes contenant également des colonnes de dates.

Ces tables de dates universelles pourront vous servir pour tous vos projets et pour toutes vos mesures DAX de type Date to Date, appelées également mesures de Time Intelligence.

💡 Après sa création, n’oubliez pas de marquer cette table comme Table de dates de référence avec le bouton Marquer comme table de dates de l’onglet Outils de table de la vue Affichage Table.

⚠️ Attention ! Marquer le Calendrier comme table de dates fera disparaître les hiérarchies de dates. Pour retrouver une hiérarchie automatique, la solution la plus simple consiste à ajouter une nouvelle colonne de dates dans la table de faits, indépendante de la colonne utilisée dans la relation. Power BI lui appliquera automatiquement sa hiérarchie native Année/Trimestre/Mois/Jour qui permet de bénéficier du drill down et des prévisions automatiques dans le visuels de type Graphique en courbes.

Utilité de certaines colonnes
  • Les colonnes commençant par Année permettront de filtrer les données sur la période correspondante : jour, semaine, mois, trimestre, semestre ou année. Elles seront utilisées dans le puits de l’axe X des abscisses pour les graphiques en courbes ou en aires.
  • Les colonnes numérique @Mois et @Jour Semaine permettent de trier dans l’ordre les colonnes texte @Mois Lettre et @Jour Lettre.
  • Les colonnes Rang vous permettront de réaliser facilement des calculs comparatifs d’une période à l’autre à partir d’une simple mesure de filtre de type :

Pour le mois en cours :

DAX
This Month = 
CALCULATE(
Expression ,
Calendrier[Rang Mois] = MAX(Calendrier[Rang Mois])
)

Affichera la valeur de l’Expression, par exemple la Somme de la colonne Quantité ou la mesure CA TOTAL, pour le dernier mois du Calendrier. Equivalent de la mesure DAX de Time Intelligence :

DAX
This Month =
TOTALMTD(
Expression,
Calendrier[Date]
)

Pour le mois précédent :

DAX
Last Month =
CALCULATE(
Expression ,
Calendrier[Rang Mois] = MAX(Calendrier[Rang Mois]) - 1
)

Affichera la valeur de l’Expression pour l’avant dernier mois. Equivalent de la mesure DAX de Time Intelligence :

DAX
Last Month =
CALCULATE(
Expression,
DATESINPERIOD(Calendrier[Date],
EOMONTH(MAX(Calendrier[Date]),-1),-1,MONTH)
)

Pour le même mois de l’année précédente :

DAX
Last Year Month =
CALCULATE(
Expression ,
Calendrier[Rang Mois] = MAX(Calendrier[Rang Mois]) - 12
)

Affichera la valeur de l’Expression pour le même mois de l’année précédente. Equivalent de la formule DAX de Time Intelligence :

DAX
Last Year Month =
CALCULATE(
Expression,
DATESMTD(SAMEPERIODLASTYEAR(Calendrier[Date]))
)

💡 On procédera de même pour les jours, les semaines, les trimestres ou les années, le calcul des variations correspondantes en sera simplifié.

Par exemple, la mesure suivante MonthOverMonth affichera la variation en + ou en – entre la valeur de l’Expression du mois en cours et celle du mois précédent en appliquant le format % personnalisé : +0.00\ %;-0.00\ %;0.00\ %

DAX
Variation MoM = 
This Month]
/
[Last Month]
- 1

💡 En complément de ces variations, la mesure suivante permettra d’afficher un cumul sur une période glissante, indicateur souvent recherché.

DAX
OneYearRolling = 
CALCULATE(
Expression ,
DATESINPERIOD(
Calendrier[Date] , 
Max(Calendrier[Date]) ,
-1 ,
YEAR)
)

Affichera la valeur de l’Expression sur une année glissante à partir d’une valeur sélectionnée dans un segment contenant une colonne périodique de type Année Mois ou Année Trimestre du Calendrier. Il suffira d’adapter la mesure en cas de recherche sur un semestre ou un trimestre glissant.