Date table creation in Power BI - Cell-by-Cell Formula Trace

Sample Data

This is a simple date table showing dates from January 1 to January 4, 2024, with columns for Year, Month, and Day extracted from the Date.

Cell	Value
A1	Date
A2	2024-01-01
A3	2024-01-02
A4	2024-01-03
A5	2024-01-04
B1	Year
B2	2024
B3	2024
B4	2024
B5	2024
C1	Month
C2	January
C3	January
C4	January
C5	January
D1	Day
D2	1
D3	2
D4	3
D5	4

Formula Trace

DateTable = ADDCOLUMNS(CALENDAR(DATE(2024,1,1), DATE(2024,1,4)), "Year", YEAR([Date]), "Month", FORMAT([Date], "MMMM"), "Day", DAY([Date]))

Step 1: CALENDAR(DATE(2024,1,1), DATE(2024,1,4))

Step 2: ADDCOLUMNS(..., "Year", YEAR([Date]))

Step 3: ADDCOLUMNS(..., "Month", FORMAT([Date], "MMMM"))

Step 4: ADDCOLUMNS(..., "Day", DAY([Date]))

Step 5: Final DateTable

Cell Reference Map

    A        B       C        D
1 | Date   | Year  | Month  | Day  |
2 | 2024-01-01 | 2024 | January | 1   |
3 | 2024-01-02 | 2024 | January | 2   |
4 | 2024-01-03 | 2024 | January | 3   |
5 | 2024-01-04 | 2024 | January | 4   |

The formula uses the Date column as the base and extracts Year, Month, and Day from it to fill columns B, C, and D.

Result

    A        | B    | C       | D
-----------------------------------
1 | Date     | Year | Month   | Day
2 | 2024-01-01 | 2024 | January | 1
3 | 2024-01-02 | 2024 | January | 2
4 | 2024-01-03 | 2024 | January | 3
5 | 2024-01-04 | 2024 | January | 4

The final date table shows each date with its corresponding year, month name, and day number in separate columns.

Sheet Trace Quiz - 3 Questions

What does the CALENDAR function do in this formula?

A Formats the month name

B Creates a list of dates between two given dates

C Calculates the year from a date

D Extracts the day number

Key Result

ADDCOLUMNS adds calculated columns (Year, Month, Day) to a date range generated by CALENDAR.