Date table creation in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate the number of days in the Date table

Given a Date table created with the following DAX expression:

Date = CALENDAR(DATE(2023,1,1), DATE(2023,12,31))

What is the result of the measure:

DayCount = COUNTROWS(Date)

?

Power BI

Date = CALENDAR(DATE(2023,1,1), DATE(2023,12,31))
DayCount = COUNTROWS(Date)

A 0

B 364

C 366

D 365

💡 Hint

Remember that 2023 is not a leap year.

The CALENDAR function creates a table with all dates from January 1, 2023 to December 31, 2023 inclusive. Since 2023 is not a leap year, there are 365 days.

❓ data_modeling

intermediate

Identify the correct way to add a Month Name column to a Date table

You have a Date table with a column [Date]. You want to add a calculated column that shows the full month name (e.g., "January", "February"). Which DAX expression correctly creates this column?

A MonthName = FORMAT(Date[Date], "MMMM")

B MonthName = DAY(Date[Date])

C MonthName = YEAR(Date[Date])

D MonthName = MONTH(Date[Date])

💡 Hint

Use FORMAT to convert dates to text month names.

The FORMAT function with "MMMM" returns the full month name from a date. MONTH returns the month number, YEAR returns the year number, and DAY returns the day number.

❓ visualization

advanced

Best visualization to show sales trend by month using a Date table

You have a Date table with a Month column and a Sales table with daily sales. You want to create a report showing total sales trend by month over a year. Which visualization type is best suited for this?

A Stacked Bar Chart

B Line Chart

C Pie Chart

D Scatter Plot

💡 Hint

Think about showing trends over time.

A Line Chart is best to show trends over time such as monthly sales. Stacked Bar Charts are better for comparing categories, Pie Charts show proportions, and Scatter Plots show relationships between two numeric variables.

🎯 Scenario

advanced

Handling missing dates in a sales dataset using a Date table

Your sales data has missing dates for some days with no sales. You want to create a report that shows daily sales including zero sales for missing dates. How can a Date table help you achieve this?

A Create a calculated column in sales to fill missing dates with zeros.

B Filter the sales table to remove missing dates before creating the report.

C Use the Date table as the axis and create a relationship to sales; use a measure that returns zero for missing sales dates.

D Use only the sales table without a Date table to avoid missing dates.

💡 Hint

Think about how Date tables provide a continuous timeline.

Using a Date table with a relationship to sales allows the report to show all dates. Measures can return zero for dates with no sales, ensuring no gaps in the timeline.

🔧 Formula Fix

expert

Identify the error in this Date table creation DAX expression

You wrote this DAX to create a Date table:

Date = CALENDAR(DATE(2023,1,1), DATE(2023,12,32))

What error will Power BI show when you try to create this table?

Power BI

Date = CALENDAR(DATE(2023,1,1), DATE(2023,12,32))

A Error: The day value 32 is invalid for month 12.

B No error, table created with dates until December 31, 2023.

C Error: Missing closing parenthesis.

D Error: CALENDAR function requires text arguments.

💡 Hint

Check the date arguments carefully.

The DATE function requires valid day values. December has only 31 days, so day 32 is invalid and causes an error.

Practice

1. What is the main purpose of creating a date table in Power BI?

easy

A. To calculate sales totals

B. To store customer names

C. To create charts automatically

D. To organize dates for easy filtering and analysis

Solution

1. Step 1: Understand the role of a date table

   A date table provides a structured list of dates to use in time-based analysis.

2. Step 2: Identify the correct purpose

   It helps with filtering, grouping, and calculating time intelligence functions.

3. Final Answer:

   To organize dates for easy filtering and analysis -> Option D

4. Quick Check:

   Date table = Organize dates [OK]

Hint: Date tables help organize and filter dates easily [OK]

Common Mistakes:

• Confusing date table with data tables like sales or customers
• Thinking date table creates charts automatically
• Assuming date table stores non-date data

2. Which DAX function is used to create a basic date table with a continuous range of dates?

easy

A. CALCULATE()

B. CALENDAR()

C. FILTER()

D. SUMMARIZE()

Solution

1. Step 1: Recall DAX functions for date tables

   CALENDAR() creates a table with dates from a start to an end date.

2. Step 2: Identify the correct function

   CALCULATE() modifies filter context, FILTER() filters rows, SUMMARIZE() groups data, so they don't create date tables.

3. Final Answer:

   CALENDAR() -> Option B

4. Quick Check:

   CALENDAR() = create date range [OK]

Hint: Use CALENDAR() to generate date ranges [OK]

Common Mistakes:

• Using CALCULATE() instead of CALENDAR()
• Confusing FILTER() with date creation
• Trying to use SUMMARIZE() to create dates

3. Given this DAX code to create a date table:

DateTable = CALENDAR(DATE(2023,1,1), DATE(2023,1,3))

What will be the number of rows in DateTable?

medium

A. 3

B. 2

C. 4

D. 1

Solution

1. Step 1: Understand CALENDAR date range

   CALENDAR includes both start and end dates in the table.

2. Step 2: Count dates from Jan 1 to Jan 3, 2023

   Dates are Jan 1, Jan 2, and Jan 3, total 3 rows.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   Inclusive dates count = 3 [OK]

Hint: Count both start and end dates in CALENDAR [OK]

Common Mistakes:

• Counting only start or end date, missing inclusive range
• Assuming CALENDAR excludes end date
• Miscounting days in the range

4. You wrote this DAX to add a Year column to your date table:

Year = YEAR(DateTable[Date])

But Power BI shows an error. What is the likely problem?

medium

A. You must create the calculated column inside the date table, not separately

B. The column name 'Year' conflicts with a reserved word

C. You cannot add calculated columns to date tables

D. The YEAR function requires a table, not a column

Solution

1. Step 1: Understand calculated columns in Power BI

   Calculated columns must be created inside the table they belong to, not as separate measures.

2. Step 2: Identify the error cause

   Writing 'Year = YEAR(DateTable[Date])' outside the table context causes error; it should be added as a new column within DateTable.

3. Final Answer:

   You must create the calculated column inside the date table, not separately -> Option A

4. Quick Check:

   Calculated columns belong inside their table [OK]

Hint: Add calculated columns inside the table, not separately [OK]

Common Mistakes:

• Trying to create calculated columns as standalone measures
• Using reserved words without renaming
• Misunderstanding YEAR function input

5. You want to create a date table from January 1, 2022 to December 31, 2023, including columns for Year, Month Number, and Month Name. Which DAX expression correctly creates this date table with these columns?

hard

A. DateTable = CALENDAR(DATE(2022,1,1), DATE(2023,12,31)) ADDCOLUMNS("Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"))

B. DateTable = CALENDAR(DATE(2022,1,1), DATE(2023,12,31)) + ADDCOLUMNS("Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"))

C. DateTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1), DATE(2023,12,31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"))

D. DateTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1), DATE(2023,12,31)), Year, YEAR(Date), MonthNumber, MONTH(Date), MonthName, FORMAT(Date, "MMMM"))

Solution

1. Step 1: Use CALENDAR to create date range

   CALENDAR(DATE(2022,1,1), DATE(2023,12,31)) creates the date table with all dates in range.

2. Step 2: Add columns with ADDCOLUMNS and correct syntax

   ADDCOLUMNS adds new columns with names as strings and expressions using [Date] column. DateTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1), DATE(2023,12,31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM")) uses correct syntax with quotes and [Date].

3. Step 3: Identify incorrect options

   DateTable = CALENDAR(DATE(2022,1,1), DATE(2023,12,31)) ADDCOLUMNS("Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM")) misses comma and chaining syntax. DateTable = CALENDAR(DATE(2022,1,1), DATE(2023,12,31)) + ADDCOLUMNS("Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM")) tries to add tables with + which is invalid. DateTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1), DATE(2023,12,31)), Year, YEAR(Date), MonthNumber, MONTH(Date), MonthName, FORMAT(Date, "MMMM")) misses quotes around column names and uses Date without brackets.

4. Final Answer:

   DateTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1), DATE(2023,12,31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM")) -> Option C

5. Quick Check:

   Correct ADDCOLUMNS syntax = DateTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1), DATE(2023,12,31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM")) [OK]

Hint: Use ADDCOLUMNS with quoted names and [Date] references [OK]

Common Mistakes:

• Missing quotes around new column names
• Using + to combine tables
• Referencing columns without brackets
• Incorrect chaining of functions