Date hierarchy (year, quarter, month, day) in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales for the Current Quarter

You have a sales dataset with a date field. Using a date hierarchy (year, quarter, month, day), which DAX expression correctly calculates the total sales for the current quarter?

A CALCULATE(SUM(Sales[Amount]), FILTER(ALL('Date'), 'Date'[Quarter] = QUARTER(TODAY()) && 'Date'[Year] = YEAR(TODAY())))

B SUM(Sales[Amount]) WHERE 'Date'[Quarter] = QUARTER(TODAY())

C SUMX(FILTER(Sales, QUARTER(Sales[Date]) = QUARTER(TODAY())), Sales[Amount])

D CALCULATE(SUM(Sales[Amount]), 'Date'[Quarter] = QUARTER(TODAY()) && 'Date'[Year] = YEAR(TODAY()))

💡 Hint

Remember to use CALCULATE with FILTER and ALL to remove filters on the date table.

Option A uses CALCULATE with FILTER and ALL to correctly filter the date table to the current quarter and year, ignoring all date filters from context. Option A uses shorthand syntax which does not fully remove other date column filters (e.g., month slicer). Option A uses incorrect syntax for accessing quarter in DAX. Option A is invalid syntax.

❓ visualization

intermediate

Best Visualization for Showing Sales Trend by Date Hierarchy

You want to show sales trends over time using a date hierarchy (year, quarter, month, day). Which Tableau visualization type is best suited for this purpose?

A Pie chart showing sales by year

B Line chart with date hierarchy on the x-axis

C Bar chart with sales by product category

D Scatter plot with sales amount vs. product price

💡 Hint

Think about how to show changes over time clearly.

A line chart with the date hierarchy on the x-axis clearly shows trends over time, allowing drill-down from year to day. Pie charts and bar charts are less effective for time trends. Scatter plots do not show time progression.

❓ data_modeling

advanced

Designing a Date Dimension Table for Hierarchy

Which of the following is the best practice when designing a date dimension table to support a date hierarchy (year, quarter, month, day) in a BI model?

A Use a single integer column representing the date as YYYYMMDD without separate hierarchy columns

B Store only the full date as a string and parse it in visuals

C Exclude the date dimension and use transaction dates directly in fact tables

D Include separate columns for Year, Quarter, Month, and Day with proper data types and relationships

💡 Hint

Think about how hierarchies work in BI tools.

Option D is best because separate columns for each level of the hierarchy allow easy filtering and grouping. Option D and C make it hard to create hierarchies. Option D reduces flexibility and performance.

🎯 Scenario

advanced

Troubleshooting Date Hierarchy Drill-Down Issue

A user reports that when drilling down from year to quarter in a Tableau dashboard, the quarters do not display correctly and some months are missing. What is the most likely cause?

A The date hierarchy is set to use fiscal quarters instead of calendar quarters

B The dashboard filter is set to exclude quarters

C The date field is not recognized as a date data type in Tableau

D The sales data has missing values for some months

💡 Hint

Check the data type of the date field in Tableau.

If the date field is not recognized as a date type, Tableau cannot correctly build the hierarchy, causing missing or incorrect quarters and months. Missing data or filters would not cause quarters to be missing in the hierarchy. Fiscal vs calendar quarters would change labels but not cause missing months.

🔧 Formula Fix

expert

Identify the Error in Date Hierarchy Calculation

Given the following DAX measure to calculate sales for the current month in a date hierarchy, what error will occur?

CurrentMonthSales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL('Date'), 'Date'[Month] = MONTH(TODAY())))

Tableau

CurrentMonthSales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL('Date'), 'Date'[Month] = MONTH(TODAY())))

A The measure will return sales for all years in the current month, not just the current year

B Syntax error due to missing YEAR filter

C Type error because MONTH() cannot be used in FILTER

D The measure will return zero because ALL('Date') removes all filters

💡 Hint

Consider what happens when filtering only by month number without year.

The measure filters all dates where the month equals the current month number, but does not restrict the year. This causes sales from all years in that month to be included. There is no syntax or type error. ALL('Date') removes filters but FILTER reapplies the month filter.