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.