DATESYTD and cumulative totals in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Year-To-Date Sales Using DATESYTD

You have a Sales table with daily sales data and a Date table linked by the Date column. You want to create a measure that calculates the total sales from the start of the year up to the current date in the filter context.

Which DAX measure correctly calculates the Year-To-Date sales?

A YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date]))

B YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD(Sales[Date]))

C YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD('Sales'[Date]))

D YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Year]))

💡 Hint

Remember that DATESYTD requires a date column from a proper Date table.

Option A correctly uses the Date table's Date column inside DATESYTD to calculate the year-to-date sales. Options B and C reference the Sales table's date column, which is not recommended for time intelligence functions. Option A uses a Year column, which is invalid for DATESYTD.

❓ visualization

intermediate

Best Visualization for Cumulative Sales Over Time

You have created a measure that calculates cumulative sales over the year using DATESYTD. You want to show this cumulative total over months in a report.

Which visualization type is best to clearly show the cumulative sales trend over time?

A Pie Chart showing cumulative sales by month

B Table showing cumulative sales by month

C Line Chart showing cumulative sales by month

D Stacked Bar Chart showing cumulative sales by month

💡 Hint

Think about which chart type best shows trends over time.

A line chart is best for showing trends and progression over time, such as cumulative sales by month. Stacked bar charts and pie charts are less effective for showing cumulative trends. Tables show data but do not visualize trends clearly.

❓ data_modeling

advanced

Handling Missing Dates in Cumulative Total Calculation

Your sales data has missing dates (no sales recorded on some days). You want to calculate a cumulative total sales measure that correctly accumulates sales over all calendar days, including those with no sales.

Which data modeling approach helps ensure the cumulative total measure works correctly?

A Create a measure that ignores the Date table and sums sales directly

B Use only the Sales table dates for cumulative calculation

C Filter out dates with no sales from the Date table

D Create a continuous Date table with all dates and relate it to Sales by Date

💡 Hint

Think about how time intelligence functions handle missing dates.

Option D is correct because a continuous Date table with all dates ensures that cumulative calculations include days with zero sales. Using only Sales table dates or filtering out no-sales dates breaks continuity and causes incorrect cumulative totals. Ignoring the Date table removes time intelligence benefits.

🔧 Formula Fix

advanced

Identify the Error in This Cumulative Total Measure

Review the following DAX measure intended to calculate cumulative sales year-to-date:

YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date], "06/30"))

What is the issue with this measure?

Power BI

YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date], "06/30"))

A The date format "06/30" is invalid; it should be a date value, not a string

B The DATESYTD function does not accept a second argument

C The measure is missing a FILTER function to limit dates

D The SUM function cannot be used inside CALCULATE

💡 Hint

Check the DATESYTD function syntax for the year-end date argument.

Option A is correct because the second argument to DATESYTD must be a date value, not a string. The string "06/30" is invalid and causes an error. DATESYTD accepts an optional year-end date as a date, e.g., DATE(Year,6,30). The other options are incorrect because DATESYTD does accept a second argument, SUM can be used inside CALCULATE, and FILTER is not required here.

🧠 Conceptual

expert

Understanding the Impact of Filter Context on Cumulative Totals

You have a cumulative sales measure using DATESYTD. When you add a slicer filtering by Product Category, the cumulative totals change unexpectedly and do not accumulate correctly over time.

What is the most likely reason for this behavior?

A The measure needs to use ALL('Date') to ignore the slicer filter

B The filter context from the Product Category slicer affects the Date table, breaking the cumulative calculation

C DATESYTD ignores all filters except on the Date column

D The relationship between Sales and Date tables is inactive

💡 Hint

Think about how slicers affect filter context and time intelligence functions.

Option B is correct because slicers on Product Category filter the Sales table, which affects the filter context on dates indirectly. This can cause cumulative totals to reset or behave unexpectedly if the measure does not handle the filter context properly. DATESYTD respects the current filter context, so filters on related tables impact the calculation. Option B is false because DATESYTD respects filters. Option B would ignore date filters, which is usually not desired. Option B is unrelated if the relationship is active.