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.