Mark as date table in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why mark a table as a date table in Power BI?

What is the main reason to mark a table as a date table in Power BI?

A To automatically create relationships between all tables in the model

B To disable filtering on that table in reports

C To convert all date columns into text format for easier filtering

D To enable time intelligence functions to work correctly with that table

💡 Hint

Think about how Power BI uses date tables for calculations like year-to-date.

Marking a table as a date table tells Power BI to treat it as the official calendar. This enables time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR, and others to work properly.

❓ dax_lod_result

intermediate

DAX measure using a marked date table

Given a date table marked as a date table and a Sales table with a 'SalesAmount' column, what is the result of this measure for the year 2023?

Sales YTD = TOTALYTD(SUM(Sales[SalesAmount]), 'Date'[Date])

Assume the Sales table has these sales amounts by date:

• 2023-01-01: 100
• 2023-06-01: 200
• 2023-12-31: 300
• 2022-12-31: 50

What is the value of Sales YTD when filtered to year 2023?

A 600

B 300

C 350

D 0

💡 Hint

YTD sums all sales from the start of the year to the current date in 2023.

The measure sums all sales in 2023: 100 + 200 + 300 = 600. The 2022 sale is excluded because the date table is marked and filters correctly.

🔧 Formula Fix

advanced

Why does TOTALYTD return blank without marking the date table?

You created a date table but forgot to mark it as a date table in Power BI. You use this measure:

Sales YTD = TOTALYTD(SUM(Sales[SalesAmount]), 'Date'[Date])

Why does this measure return blank values?

A Because TOTALYTD requires the date table to be marked to recognize the date column for time intelligence

B Because the relationship between Sales and Date tables is inactive

C Because the date column has text data type instead of date

D Because the Sales table has no data for the current year

💡 Hint

Think about how Power BI knows which table is the official calendar.

Without marking the date table, Power BI does not know which table to use for time intelligence calculations, so TOTALYTD returns blank.

❓ visualization

advanced

Best visualization to show monthly sales trend using a marked date table

You want to show monthly sales trends over a year using a date table marked as a date table. Which visualization type is best to clearly show this trend?

A Stacked bar chart with months as legend

B Pie chart showing sales by month

C Line chart with 'Date'[Month] on the axis and sum of sales on values

D Table showing sales by month and product

💡 Hint

Think about which chart type best shows trends over time.

Line charts are best for showing trends over time, such as monthly sales. Pie charts and stacked bars are less effective for trends.

❓ data_modeling

expert

Handling multiple date columns with one marked date table

Your Sales table has two date columns: 'OrderDate' and 'ShipDate'. You have one date table marked as a date table. How do you model relationships to use time intelligence on both dates?

A Mark the date table twice, once for each date column

B Create inactive relationships from the date table to both 'OrderDate' and 'ShipDate', then use USERELATIONSHIP in measures for ShipDate

C Create two separate date tables, each marked as a date table, one for each date column

D Create active relationships from the date table to both 'OrderDate' and 'ShipDate'

💡 Hint

Power BI allows only one active relationship per table pair.

Only one active relationship can exist between tables. The other must be inactive and activated in DAX using USERELATIONSHIP for calculations on the second date.

Practice

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

easy

A. To automatically generate charts from the table

B. To tell Power BI which table contains the calendar dates for time intelligence

C. To create a relationship between two unrelated tables

D. To hide the table from report view

Solution

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

   A date table contains a list of dates used for time-based calculations and filtering.

2. Step 2: Recognize what marking a date table does

   Marking a table as a date table tells Power BI to use it for time intelligence functions like year-to-date or month-to-date calculations.

3. Final Answer:

   To tell Power BI which table contains the calendar dates for time intelligence -> Option B

4. Quick Check:

   Mark as date table = Identify calendar table [OK]

Hint: Mark date table to enable time intelligence easily [OK]

Common Mistakes:

• Confusing marking date table with creating relationships
• Thinking it hides the table
• Assuming it auto-generates visuals

2. Which of the following is the correct way to mark a table as a date table in Power BI Desktop?

easy

A. Create a new measure with DAX using CALENDAR function

B. Rename the table to 'DateTable'

C. Drag the date column to the Filters pane

D. Right-click the table > Mark as Date Table > Select the date column

Solution

1. Step 1: Locate the marking option in Power BI Desktop

   In the Fields pane, right-click the table you want to mark as a date table.

2. Step 2: Apply the mark as date table action

   Choose 'Mark as Date Table' and then select the column that contains unique dates.

3. Final Answer:

   Right-click the table > Mark as Date Table > Select the date column -> Option D

4. Quick Check:

   Mark date table via right-click menu [OK]

Hint: Right-click table to mark date table quickly [OK]

Common Mistakes:

• Trying to mark via DAX measure
• Dragging date column to filters instead
• Renaming table does not mark it

3. Given a date table named 'Calendar' with a column 'Date' containing unique continuous dates, what will be the result of this DAX measure after marking 'Calendar' as a date table?

YTD Sales = TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

medium

A. Calculates year-to-date sales correctly using the Calendar table

B. Returns an error because TOTALYTD requires a numeric column

C. Ignores the Calendar table and sums all sales

D. Calculates sales only for the current month

Solution

1. Step 1: Understand TOTALYTD function

   TOTALYTD sums values from the Sales table filtered by dates up to the current date in the specified date column.

2. Step 2: Effect of marking the Calendar table

   Marking 'Calendar' as a date table enables Power BI to use Calendar[Date] properly for time intelligence calculations like YTD.

3. Final Answer:

   Calculates year-to-date sales correctly using the Calendar table -> Option A

4. Quick Check:

   Marked date table enables correct TOTALYTD [OK]

Hint: Mark date table to enable TOTALYTD calculations [OK]

Common Mistakes:

• Thinking TOTALYTD needs numeric date column
• Assuming it sums all sales ignoring date
• Confusing YTD with monthly totals

4. You marked a table as a date table but receive incorrect time intelligence results. What is the most likely cause?

medium

A. The date column contains duplicate or missing dates

B. The table is not connected to any other table

C. The date column is formatted as text

D. The table has too many columns

Solution

1. Step 1: Check date column uniqueness

   Marking a date table requires the date column to have unique, continuous dates without gaps.

2. Step 2: Understand impact of duplicates or gaps

   If duplicates or missing dates exist, time intelligence functions may produce wrong or unexpected results.

3. Final Answer:

   The date column contains duplicate or missing dates -> Option A

4. Quick Check:

   Unique continuous dates needed for correct time intelligence [OK]

Hint: Ensure date column has unique continuous dates [OK]

Common Mistakes:

• Ignoring duplicates in date column
• Blaming table relationships first
• Thinking column format alone causes errors

5. You have a sales table and a calendar table with a 'Date' column. You want to create a report showing monthly sales trends using time intelligence. Which steps correctly apply marking as date table and relationships to achieve this?

hard

A. Create a relationship from sales[Date] to calendar[Date], no need to mark any table as date table

B. Mark the sales table as date table, create a relationship from calendar[Date] to sales[Date], no need for unique dates

C. Mark the calendar table as date table, ensure 'Date' column is unique, create a relationship from sales[Date] to calendar[Date]

D. Mark both tables as date tables and create a many-to-many relationship between their date columns

Solution

1. Step 1: Mark the calendar table as date table

   The calendar table must be marked as date table with a unique continuous 'Date' column for time intelligence.

2. Step 2: Create a relationship from sales to calendar

   Establish a one-to-many relationship from sales[Date] (many) to calendar[Date] (one) to filter sales by calendar dates.

3. Final Answer:

   Mark the calendar table as date table, ensure 'Date' column is unique, create a relationship from sales[Date] to calendar[Date] -> Option C

4. Quick Check:

   Mark calendar and relate sales date to calendar date [OK]

Hint: Mark calendar table and link sales date to it [OK]

Common Mistakes:

• Marking sales table instead of calendar
• Skipping marking date table
• Creating wrong relationship direction or type