Post

Power Query - Optimised Calendar/Dates Table

Enjoy a newly refurbished calendar table dimension with business calendar and fiscal calendar date fields 🕊️🧙🏼‍♂️✨

Posted
IMAGE_DESCRIPTION
IMAGE_DESCRIPTION
By Imran Haq , PBI DataVizzle
8 min read
Power Query - Optimised Calendar/Dates Table

💌 PBIX file available at the end of the article 1 Enjoy!

 Intro

The calendar / dates table is easily THE most important dimension table in any semantic model. Most of you will have came across Melissa de Korte’s famous Extended Date Table no doubt. My date table builds upon Melissa’s great work even further additional fields and a dynamic 3-in-1 dates table:

  • Calendar Year
  • Business Year
  • Financial Year

Column selection is dynamic, so Business & Financial Year fields are only selected if Business / Fiscal Start Month is input in the function parameters.


. . .

 Build

You can begin debugging the code directly the PBIX1 linked in the footnotes. In the next section, we’ll take a look at some important code snippets.

. . .

 Code

In this Code segment, we will look some (but not all) of the most important transformations and useful patterns used to calculate the requisite calendar and fiscal year fields. We will use a combination of M-code and pseudo-code so we can better understand some of the underlying mechanics behind the calendar table values:

Calendar Table Transformation Steps

Parameters

1
2
3
4

p_start_date = #date(p_year_start,01,01) // #date( 2024 , 01 , 01 )
p_end_date = #date(p_year_end,12,31) // #date( 2027 , 12, 31 )
v_day_count = Duration.Days(Duration.From(p_end_date -  p_start_date)) + 1
// Duration.Days ( Duration.From (#date( 2027 , 12, 31 ) - #date( 2024 , 01 , 01 )  )) + 1

Contiguous List of Dates

1

v_source = List.Dates(p_start_date, v_day_count, Duration.From(1))
List
01/01/2024
02/01/2024
03/01/2024
04/01/2024
…etc
30/12/2027
31/12/2027


Generate Date Serial Number

We can derive a date serial number which can act as an alternative key.

1
2
3
4
5
6
7

v_date_serial = Table.AddColumn(
    v_to_table, 
    "date_serial_number", 
    each Duration.Days([date_id] - #date(1900, 1, 1))
    , Int64.Type
)
date_iddate_serial_number
01/01/202445290
02/01/202445291
03/01/202445292
04/01/202445293
…etc…etc
30/12/202746019
31/12/202746020


Generate Year-specific Columns

The following examples are a brilliant use case for record fields. This is one of the first optimisations where instead of creating 8 separate steps for 8 separate columns, we can create 8 columns in 1 step. Here’s how we do it, note the square brackets on each [...]. Every variable inside the square brackets is a new column:

psuedo-code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

current_step = Table.AddColumn(
    previous_step,
    "column_name",
each [
    column1 = formula1,
    column2 = formula2,
    column3 = formula3
],
type [
    column1 = Text.Type, // column data type
    column2 = Date.Type, // column data type
    column3 = Int64.Type // column data type
]

)
real-code
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

v_rec_years = Table.AddColumn(
  v_date_serial, 
  "rec_year", 
  each [
    // get calendar year
    calendar_year        = Date.Year([date_id]), 

    // get current year: Date.Year( #date( 2025, 04 , 21 ) )
    current_year         = Date.Year(v_current_date), 

    // get current year offset: 2024 - 2025
    calendar_year_offset = calendar_year - current_year, 

    // check if the calendar year is complete
    is_year_complete     = if Date.EndOfYear([date_id]) < Date.EndOfYear(v_current_date) then 1 else 0, 

    // check if date is completed month to date (cmtd)
    is_cmtd              = if ([date_id] <= Date.AddDays(Date.StartOfMonth(Date.From(v_current_date)), - 1)) then 1 else 0, 

    //check if date is year-to-date (ytd)
    is_ytd               = if ([date_id] <= Date.From(v_current_date)) then 1 else 0, 

    // check if date is forecast/future (frc)
    is_frc               = if ([date_id] > Date.From(v_current_date)) then 1 else 0, 

    // check if date is in the previous year (py)
    is_py                = if calendar_year_offset = - 1 then 1 else 0
  ], 
  type [
    calendar_year = Int64.Type, 
    calendar_year_offset = Int64.Type, 
    is_year_complete = Int64.Type, 
    is_cmtd = Int64.Type, 
    is_ytd = Int64.Type, 
    is_frc = Int64.Type, 
    is_py = Int64.Type
  ]
)
date_iddate_serial_numbercalendar_year_offsetis_year_completeis_cmtdis_ytd
29/03/2025457430011
30/03/2025457440011
31/03/2025457450011
01/04/2025457460001
02/04/2025457470001
03/04/2025457480001


Generate Fiscal Calendar Fields

Next we will demonstrate how we can derive the fiscal year columns. This method can be reused and reapplied to the business column fields. The variable names will become the columns

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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

v_rec_fiscal_fields = = Table.AddColumn(
    v_exp_rec_business_fields, 
    "BusinessRecords", 
    each [
    fiscal_year_num = (                     // fiscal year number
        if [month_num] >= v_fy_start_month 
        and v_fy_start_month > 1 
        then [calendar_year] + 1 
        else [calendar_year]
    ), 
    fiscal_year_offset = (                  // calendar year minus
        if [month_num] >= v_fy_start_month 
        then ([calendar_year] + 1) 
        else [calendar_year]
        )   
        - // minus
        (                                   // current year
        if v_current_month_num >= v_fy_start_month 
        then (Date.Year(v_current_date) + 1) 
        else Date.Year(v_current_date)
    ),
    fiscal_year = // eg: 24/25
        Text.End(Text.From(fiscal_year_num -1), 2) // Text.End(2024, 2)
        & "/" & 
        Text.End(Text.From(fiscal_year_num ), 2), // Text.End(2025, 2)

    // "FY" & Text.End(2025,2) = "FY25"
    fiscal_year_short = "FY" & Text.End(Text.From(fiscal_year_num), 2), 

    // field to enable default "Current Fiscal Year" slicer selection
    fiscal_year_selection = if fiscal_year_offset = 0 then "Current Fiscal Year" else fiscal_year,

    // flag current fiscal year
    is_current_fy = if fiscal_year_offset = 0 then 1 else 0,

    // flag previous fiscal year
    is_previous_fy = if fiscal_year_offset = -1 then 1 else 0,

    // fiscal quarter number = Number.RoundUp( Date.Month ( Date.AddMonths( 8 - (4-1) )) / 3 )
    fiscal_quarter_num = Number.RoundUp( Date.Month( Date.AddMonths( [date_id], - (v_fy_start_month -1) )) / 3 ) ,

    // fiscal quarter label - eg: "FQ4"
    fiscal_quarter = "FQ" & Text.From( fiscal_quarter_num ) ,

    // fiscal quarter - eg: "FQ4 FY25"
    fiscal_year_quarter = fiscal_quarter & " " & fiscal_year, 

    // fiscal quarter numbumer - eg: (2024 * 100) + 04 = 202404
    fiscal_year_quarter_num = (fiscal_year_num * 100) + fiscal_quarter_num, 

    // fiscal period number - eg: FY Start April, Fiscal Number = April = 1, May = 2 etc
    fiscal_period_num = 
        if [month_num] >= v_fy_start_month and v_fy_start_month > 1 
        then  [month_num] - (v_fy_start_month - 1)
        else if [month_num] >= v_fy_start_month and v_fy_start_month = 1 
        then  [month_num]
        else  [month_num] + (12 - v_fy_start_month + 1), 

    // fiscal period - eg: FP01 24/25
    fiscal_period = "FP" & Text.PadStart(Text.From(fiscal_period_num), 2, "0") & " " & fiscal_year  , 

    // fiscal year month num = (2024 * 100 ) + 04
    fiscal_year_month_num = ((fiscal_year_num * 100) + fiscal_period_num), 

    // fiscal month name short = "Apr"
    fiscal_month_name_short = [month_name_short],

    // fiscal month year = "Apr-24"
    fiscal_month_year = [month_year],


    fiscal_year_first_day = #date(Date.Year(p_start_date) - 1, v_fy_start_month, 1), 
    
    // fiscal year start
    fiscal_year_start = 
        if [month_num] >= v_fy_start_month 
        then #date([calendar_year], v_fy_start_month, 1) 
        else #date([calendar_year] - 1, v_fy_start_month, 1),

    // fiscal year end
    fiscal_year_end = 
        if [month_num] >= v_fy_start_month 
        then Date.AddDays(#date([calendar_year] +1, v_fy_start_month, 1), -1) 
        else Date.AddDays(#date([calendar_year] + 0, v_fy_start_month, 1),-1),

    // fiscal week start
    fiscal_week_start = Date.StartOfWeek(fiscal_year_first_day, v_week_start),

    // fiscal week end
    current_week_start = Date.StartOfWeek([date_id], v_week_start),

    // fiscal week difference
    week_diff = (Duration.Days(current_week_start - fiscal_week_start) / 7),

    // fiscal week number
    fiscal_week_num = Number.RoundDown(week_diff) + 1,

    // fiscal week
    fiscal_week = "FW" & Text.PadStart( Text.From(fiscal_week_num), 2, "0") & " " & fiscal_year,

    // fiscal week logic
    fiscal_week_logic = v_fiscal_week_logic,

    // fiscal week logic update
    fiscal_week_logic_updated = if fiscal_week_logic then Table.ReplaceValue(fiscal_year_first_day, each fiscal_week_num, each if v_fy_start_month =1 then [week_num] else fiscal_week_num, Replacer.ReplaceValue, {"fiscal_week_num"}) else fiscal_year_first_day,

    // fiscal year week number = if v_fiscal_week_logic = true then [week_year_num] else (2024 * 100) + 12
    fiscal_year_week_num = if v_fiscal_week_logic then [week_year_num] else (fiscal_year_num * 100) + fiscal_week_num

    ], 

    // declare column data types inside the record
    type [
    fiscal_year_num = number, 
    fiscal_year_offset = Int64.Type, 
    fiscal_year = text, 
    fiscal_year_short = text,
    fiscal_year_selection = text,

    is_current_fy = Int64.Type,
    is_previous_fy = Int64.Type,

    fiscal_quarter_num = number,
    fiscal_quarter = text,
    fiscal_year_quarter_num = number, 
    fiscal_year_quarter = text, 

    fiscal_period_num = number, 
    fiscal_period = text, 
    fiscal_month_name_short = text,
    fiscal_year_month_num = number, 
    fiscal_month_year = text,

    fiscal_year_first_day = date,
    fiscal_year_start = date,
    fiscal_year_end = date ,
    fiscal_week_num = number,
    fiscal_week = text,
    fiscal_week_logic = logical,
    fiscal_week_logic_updated = date,
    fiscal_year_week_num = number
    ]
)
example of fiscal column/record values
field namefield value
fiscal_year_num2025
fiscal_year_offset-1
fiscal_year24/25
fiscal_year_shortFY25
fiscal_year_selection24/25
is_current_fy0
is_previous_fy1
fiscal_quarter_num4
fiscal_quarterFQ4
fiscal_year_quarterFQ4 24/25
fiscal_year_quarter_num202504
fiscal_period_num12
fiscal_periodFP12 24/25
fiscal_year_month_num202512
fiscal_month_name_shortMar
fiscal_month_yearMar-25
fiscal_year_first_day01/04/2023
fiscal_year_start01/04/2024
fiscal_year_end31/03/2025
fiscal_week_start27/03/2023
current_week_start24/03/2025
week_diff104
fsical_week_num105
fiscal_weekFW105 24/25
fiscal_week_logicFALSE
fiscal_week_logic_updated01/04/2023
fiscal_year_week_num202605


. . .

I hope you can see how much more efficient and versatile it is to use records to derivce multiple columns in one step. There are many other little treats and techniques inside the PBIX1.

Function Meta

. . .

  En Fin, Serafin

Thank you for staying to the end of the article… I hope you find it useful 😊. See you soon, and remember… #StayQueryous!🧙‍♂️🪄

  PBIX 💾

🔗 Repo: Power Query Showcases
🔗 Repo: Github Repo PBIX Treasure Trove

"Buy Me A Coffee"

. . .

Footnotes

  1. PBIX: Calendar Dates Table Template ↩︎ ↩︎2 ↩︎3

PowerQuery
powerquery walkthrough
This post is licensed under CC BY 4.0 by the author.