DATESYTD and cumulative totals in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the year-to-date sales total using DATESYTD.

Power BI

Total Sales YTD = CALCULATE(SUM(Sales[Amount]), [1](Calendar[Date]))

Drag options to blanks, or click blank then click option'

ADATESMTD

BTOTALYTD

CDATESYTD

DDATESQTD

Attempts:

3 left

💡 Hint

Common Mistakes

Using TOTALYTD instead of DATESYTD inside CALCULATE.

Using DATESMTD which is for month-to-date, not year-to-date.

The DATESYTD function returns a set of dates from the start of the year to the current date, which is perfect for calculating year-to-date totals.

2fill in blank

medium

Complete the code to create a cumulative total measure that sums sales up to the current date.

Power BI

Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar), Calendar[Date] <= [1]))

Drag options to blanks, or click blank then click option'

AMAX(Calendar[Date])

BMAX(Sales[Date])

CMIN(Calendar[Date])

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX(Sales[Date]) which may not be related to the calendar table.

Using MIN(Calendar[Date]) which gives the earliest date, not the current.

Using MAX(Calendar[Date]) gets the current date in the filter context to sum all sales up to that date.

3fill in blank

hard

Fix the error in this cumulative total measure by completing the blank.

Power BI

Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL([1]), Calendar[Date] <= MAX(Calendar[Date])))

Drag options to blanks, or click blank then click option'

ACalendar

BSales

CCustomers

DProducts

Attempts:

3 left

💡 Hint

Common Mistakes

Using ALL(Sales) which does not contain the Date column.

Using ALL(Customers) or ALL(Products) which are unrelated to dates.

The ALL(Calendar) removes filters from the Calendar table so the FILTER can compare all dates properly.

4fill in blank

hard

Fill both blanks to calculate the year-to-date cumulative sales correctly.

Power BI

YTD Cumulative Sales = CALCULATE(SUM(Sales[Amount]), [1](Calendar[Date]), FILTER(ALL(Calendar), Calendar[Date] <= [2]))

Drag options to blanks, or click blank then click option'

ADATESYTD

BTOTALYTD

CMAX(Calendar[Date])

DMIN(Calendar[Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using TOTALYTD instead of DATESYTD inside CALCULATE.

Using MIN(Calendar[Date]) which filters incorrectly.

Use DATESYTD to get year-to-date dates and MAX(Calendar[Date]) to filter dates up to the current date.

5fill in blank

hard

Fill all three blanks to create a cumulative total measure that resets each year.

Power BI

Cumulative Sales YTD = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar), Calendar[Date] <= [1] && YEAR(Calendar[Date]) = [2]), VALUES(Calendar[[3]]))

Drag options to blanks, or click blank then click option'

AMAX(Calendar[Date])

BYEAR(MAX(Calendar[Date]))

CYear

DMonth

Attempts:

3 left

💡 Hint

Common Mistakes

Using Month instead of Year in the last blank.

Using TODAY() instead of MAX(Calendar[Date]) for the date.

The measure sums sales up to the max date (MAX(Calendar[Date])) within the current year (YEAR(MAX(Calendar[Date]))) and filters by the Year column (Year).

Practice

1. What does the DAX function DATESYTD do in Power BI?

easy

A. Returns the last date of the year

B. Calculates the total sales for the entire year

C. Filters dates only for the current month

D. Returns all dates from the start of the year up to the current date

Solution

1. Step 1: Understand the purpose of DATESYTD

   DATESYTD returns a table of dates starting from January 1st of the current year up to the specified date, usually today.

2. Step 2: Compare options with definition

   Only Returns all dates from the start of the year up to the current date correctly describes this behavior, while others describe different date filters or totals.

3. Final Answer:

   Returns all dates from the start of the year up to the current date -> Option D

4. Quick Check:

   DATESYTD = Year start to today [OK]

Hint: DATESYTD always starts from Jan 1 to current date [OK]

Common Mistakes:

• Confusing DATESYTD with monthly filters
• Thinking it returns totals instead of dates
• Assuming it returns dates for the whole year

2. Which of the following is the correct syntax to calculate year-to-date sales using DATESYTD in DAX?

easy

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

B. YTD Sales = SUM(Sales[Amount]) + DATESYTD(Calendar[Date])

C. YTD Sales = FILTER(Sales, DATESYTD(Calendar[Date]))

D. YTD Sales = CALCULATE(DATESYTD(Sales[Amount]), Calendar[Date])

Solution

1. Step 1: Identify correct use of CALCULATE and DATESYTD

   CALCULATE modifies filter context; DATESYTD returns dates. The correct syntax uses CALCULATE with SUM and DATESYTD as filter.

2. Step 2: Check each option

   YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD(Calendar[Date])) correctly uses CALCULATE(SUM(...), DATESYTD(...)). Others misuse functions or order.

3. Final Answer:

   YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD(Calendar[Date])) -> Option A

4. Quick Check:

   CALCULATE + SUM + DATESYTD = correct syntax [OK]

Hint: Use CALCULATE with SUM and DATESYTD as filter [OK]

Common Mistakes:

• Adding DATESYTD directly to SUM
• Using FILTER instead of CALCULATE
• Passing measure inside DATESYTD

3. Given the following DAX measure:

Running Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar[Date]), Calendar[Date] <= MAX(Calendar[Date])))

What does this measure calculate?

medium

A. Total sales for the current date only

B. Sales only for dates after the current date

C. Cumulative sales from the earliest date up to the current date

D. Sales filtered by the current month

Solution

1. Step 1: Analyze FILTER and ALL usage

   ALL(Calendar[Date]) removes filters on dates, FILTER keeps dates less than or equal to MAX date in context, creating a running window.

2. Step 2: Understand CALCULATE with SUM

   SUM aggregates sales over this filtered date range, resulting in cumulative total up to current date.

3. Final Answer:

   Cumulative sales from the earliest date up to the current date -> Option C

4. Quick Check:

   FILTER + ALL + <= MAX = running total [OK]

Hint: FILTER with ALL and <= MAX gives running total [OK]

Common Mistakes:

• Thinking it sums only current date
• Confusing cumulative with monthly totals
• Ignoring ALL removes filters

4. You wrote this DAX measure to calculate year-to-date sales:

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

But it returns an error. What is the likely cause?

medium

A. Sales[Amount] should be replaced with Sales[Date]

B. DATESYTD requires a date column from a calendar table, not from the Sales table

C. DATESYTD needs two arguments, but only one is provided

D. SUM cannot be used inside CALCULATE

Solution

1. Step 1: Check argument for DATESYTD

   DATESYTD expects a continuous date column, usually from a calendar table, not transactional tables like Sales.

2. Step 2: Identify error cause

   Using Sales[Date] (likely non-continuous) causes error; correct is to use Calendar[Date] from a proper date table.

3. Final Answer:

   DATESYTD requires a date column from a calendar table, not from the Sales table -> Option B

4. Quick Check:

   DATESYTD needs calendar date column [OK]

Hint: Use calendar table date column with DATESYTD [OK]

Common Mistakes:

• Using transactional table date column
• Assuming SUM can't be inside CALCULATE
• Thinking DATESYTD needs two arguments

5. You want to create a cumulative total measure that resets at the start of each year. Which DAX expression correctly achieves this?

hard

A. Cumulative Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar[Date]), YEAR(Calendar[Date]) = YEAR(MAX(Calendar[Date])) && Calendar[Date] <= MAX(Calendar[Date])))

B. Cumulative Total = CALCULATE(SUM(Sales[Amount]), DATESYTD(Sales[Date]))

C. Cumulative Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar[Date]), Calendar[Date] <= MAX(Calendar[Date])))

D. Cumulative Total = SUMX(FILTER(Calendar, Calendar[Date] <= MAX(Calendar[Date])), Sales[Amount])

Solution

1. Step 1: Understand requirement to reset cumulative each year

   The cumulative total must sum sales from the start of the current year to the current date, resetting yearly.

2. Step 2: Analyze each option

   Cumulative Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar[Date]), Calendar[Date] <= MAX(Calendar[Date]))) sums from earliest date ever, no reset. Cumulative Total = CALCULATE(SUM(Sales[Amount]), DATESYTD(Sales[Date])) uses DATESYTD(Sales[Date]), which requires a date column from a calendar table. Cumulative Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar[Date]), YEAR(Calendar[Date]) = YEAR(MAX(Calendar[Date])) && Calendar[Date] <= MAX(Calendar[Date]))) filters dates to current year and up to current date, correctly resetting yearly. Cumulative Total = SUMX(FILTER(Calendar, Calendar[Date] <= MAX(Calendar[Date])), Sales[Amount]) sums incorrectly without filter context.

3. Final Answer:

   Cumulative Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar[Date]), YEAR(Calendar[Date]) = YEAR(MAX(Calendar[Date])) && Calendar[Date] <= MAX(Calendar[Date]))) -> Option A

4. Quick Check:

   Filter by year and date <= MAX for yearly reset [OK]

Hint: Filter dates by year and <= MAX date for yearly cumulative [OK]

Common Mistakes:

• Not filtering by year causes cumulative to never reset
• Using DATESYTD with Sales[Date] instead of Calendar[Date]
• Ignoring ALL removes filters incorrectly