TOTALYTD, TOTALQTD, TOTALMTD in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Year-To-Date Sales Using TOTALYTD

Given a Sales table with a Date column and a SalesAmount column, what is the result of this measure on 2023-03-15?

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

A Sum of SalesAmount from 2023-01-01 to 2023-03-15

B Sum of SalesAmount for the entire year 2023

C Sum of SalesAmount for the month of March 2023

D Sum of SalesAmount for the quarter containing March 2023

💡 Hint

TOTALYTD sums values from the start of the year up to the given date.

TOTALYTD calculates the sum from the first day of the year to the specified date, so it includes January 1 to March 15 sales.

❓ dax_lod_result

intermediate

Calculate Quarter-To-Date Sales Using TOTALQTD

What does this measure return on 2023-05-10?

Sales QTD = TOTALQTD(SUM(Sales[SalesAmount]), Sales[Date])

A Sum of SalesAmount for the entire year 2023

B Sum of SalesAmount from 2023-01-01 to 2023-05-10

C Sum of SalesAmount for the month of May 2023

D Sum of SalesAmount from 2023-04-01 to 2023-05-10

💡 Hint

TOTALQTD sums values from the start of the quarter to the given date.

TOTALQTD sums SalesAmount starting from the first day of the quarter (April 1) to May 10, 2023.

❓ dax_lod_result

advanced

Calculate Month-To-Date Sales Using TOTALMTD with Filter

Consider this measure:

Sales MTD Filtered = TOTALMTD(SUM(Sales[SalesAmount]), Sales[Date], Sales[Region] = "West")

What is the result on 2023-07-20?

A Sum of SalesAmount for all regions from 2023-07-01 to 2023-07-20

B Sum of SalesAmount for Region West from 2023-07-01 to 2023-07-20

C Sum of SalesAmount for Region West for the entire July 2023

D Sum of SalesAmount for Region West for the entire year 2023

💡 Hint

TOTALMTD sums values from the start of the month to the given date, applying the filter.

The measure sums SalesAmount only for Region West from July 1 to July 20, 2023.

❓ visualization

advanced

Best Visualization to Compare YTD, QTD, and MTD Sales

You want to show Year-To-Date, Quarter-To-Date, and Month-To-Date sales side by side for each product category. Which visualization type is best?

A Line chart with time on X-axis and sales amount on Y-axis showing YTD, QTD, and MTD as lines

B Pie chart showing combined sales of YTD, QTD, and MTD for all categories

C Clustered column chart with product categories on X-axis and three bars per category for YTD, QTD, and MTD

D Stacked area chart showing cumulative sales over the year

💡 Hint

You want to compare three values side by side for each category.

A clustered column chart clearly shows YTD, QTD, and MTD sales side by side for each product category, making comparison easy.

🧠 Conceptual

expert

Understanding Behavior of TOTALYTD with Non-Standard Fiscal Year

Your company’s fiscal year starts on April 1. You use TOTALYTD without specifying the year-end date. What will happen when calculating YTD sales on June 15, 2023?

A TOTALYTD will calculate from January 1, 2023, to June 15, 2023, ignoring the fiscal year start

B TOTALYTD will calculate from April 1, 2023, to June 15, 2023, automatically detecting fiscal year

C TOTALYTD will return an error because fiscal year start is not specified

D TOTALYTD will calculate from April 1, 2022, to June 15, 2023

💡 Hint

By default, TOTALYTD assumes calendar year starting January 1.

Without specifying the fiscal year-end date, TOTALYTD uses the calendar year starting January 1, so it sums from January 1, 2023, to June 15, 2023.

Practice

1. What does the TOTALYTD function do in Power BI?

easy

A. Calculates the average of values for the entire year.

B. Calculates the sum of values for the current month only.

C. Calculates the sum of values for the current quarter only.

D. Calculates the sum of values from the start of the year to the current date.

Solution

1. Step 1: Understand the purpose of TOTALYTD

   TOTALYTD sums values from the beginning of the year up to the current date.

2. Step 2: Compare with other time functions

   TOTALMTD sums for the month, TOTALQTD sums for the quarter, so TOTALYTD is for the year.

3. Final Answer:

   Calculates the sum of values from the start of the year to the current date. -> Option D

4. Quick Check:

   TOTALYTD = Year-to-date sum [OK]

Hint: YTD means year-to-date sum from Jan 1 to today [OK]

Common Mistakes:

• Confusing TOTALYTD with TOTALMTD or TOTALQTD
• Thinking it calculates average instead of sum
• Using it without a proper date column

2. Which of the following is the correct syntax to calculate the quarter-to-date total sales using TOTALQTD?

easy

A. TOTALQTD(SUM(Sales[Amount]), Sales[Date])

B. TOTALQTD(Sales[Amount], Sales[Date])

C. TOTALQTD(SUM(Sales[Amount]))

D. TOTALQTD(Sales[Date], SUM(Sales[Amount]))

Solution

1. Step 1: Identify correct function parameters

   TOTALQTD requires an aggregation (like SUM) as first argument and a date column as second.

2. Step 2: Check each option

   TOTALQTD(SUM(Sales[Amount]), Sales[Date]) uses SUM(Sales[Amount]) and Sales[Date] correctly. Others miss SUM or swap arguments.

3. Final Answer:

   TOTALQTD(SUM(Sales[Amount]), Sales[Date]) -> Option A

4. Quick Check:

   Syntax = TOTALQTD(SUM(column), date) [OK]

Hint: First argument SUM, second argument date column [OK]

Common Mistakes:

• Omitting SUM aggregation
• Swapping argument order
• Using a non-date column as date argument

3. Given the following DAX measure:

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

If today is March 15, 2024, and sales amounts are:
- Jan: 1000
- Feb: 1500
- Mar (up to 15th): 500
What is the value of Sales YTD?

medium

A. 1500

B. 500

C. 3000

D. 2500

Solution

1. Step 1: Understand TOTALYTD calculation

   TOTALYTD sums all sales from Jan 1 to current date (March 15).

2. Step 2: Add sales amounts up to March 15

   Jan (1000) + Feb (1500) + Mar (500) = 3000 total.

3. Final Answer:

   3000 -> Option C

4. Quick Check:

   YTD sum = 1000+1500+500 = 3000 [OK]

Hint: Add all months from Jan 1 to current date [OK]

Common Mistakes:

• Only summing current month
• Ignoring partial month sales
• Confusing quarter-to-date with year-to-date

4. You wrote this measure:

Sales QTD = TOTALQTD(Sales[Amount], Sales[Date])

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

medium

A. Missing aggregation function like SUM around Sales[Amount].

B. Date column Sales[Date] is not sorted.

C. TOTALQTD cannot be used with date columns.

D. Measure name cannot contain spaces.

Solution

1. Step 1: Check function arguments

   TOTALQTD requires an aggregation function like SUM as first argument, not a column alone.

2. Step 2: Identify error cause

   Using Sales[Amount] directly causes syntax error; wrapping with SUM fixes it.

3. Final Answer:

   Missing aggregation function like SUM around Sales[Amount]. -> Option A

4. Quick Check:

   Use SUM(column) inside TOTALQTD [OK]

Hint: Always wrap value column with SUM inside TOTALQTD [OK]

Common Mistakes:

• Passing column directly without aggregation
• Assuming date column sorting causes error
• Thinking measure names cannot have spaces

5. You want to create a measure that shows the month-to-date sales but resets correctly at the start of each quarter. Which DAX expression should you use?

hard

A. TOTALMTD(SUM(Sales[Amount]), Sales[Date])

B. CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Date], STARTOFQUARTER(Sales[Date]), TODAY()))

C. TOTALQTD(SUM(Sales[Amount]), Sales[Date])

D. CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Date], STARTOFMONTH(Sales[Date]), TODAY()))

Solution

1. Step 1: Understand requirement

   Measure must sum sales from start of each quarter up to today (month-to-date but resets quarterly).

2. Step 2: Analyze options

   TOTALMTD(SUM(Sales[Amount]), Sales[Date]) sums month-to-date but resets monthly, not quarterly.
   TOTALQTD(SUM(Sales[Amount]), Sales[Date]) sums quarter-to-date, not month-to-date.
   CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Date], STARTOFMONTH(Sales[Date]), TODAY())) sums month-to-date using CALCULATE but resets monthly.
   CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Date], STARTOFQUARTER(Sales[Date]), TODAY())) sums sales from start of quarter to today, effectively month-to-date within quarter.

3. Final Answer:

   CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Date], STARTOFQUARTER(Sales[Date]), TODAY())) -> Option B

4. Quick Check:

   Use DATESBETWEEN with STARTOFQUARTER for quarterly reset [OK]

Hint: Use DATESBETWEEN with STARTOFQUARTER for custom MTD reset [OK]

Common Mistakes:

• Using TOTALMTD which resets monthly, not quarterly
• Confusing TOTALQTD with month-to-date logic
• Not using CALCULATE with date range for custom resets