TOTALYTD, TOTALQTD, TOTALMTD in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see how sales have performed over time, specifically the total sales year-to-date, quarter-to-date, and month-to-date.

📊 Data: You have a sales table with daily sales data including Date, Sales Amount, and Region.

🎯 Deliverable: Create a Power BI report with three measures showing TOTALYTD, TOTALQTD, and TOTALMTD sales and visualize them in a line chart by date.

Sample Data

Date	Sales Amount	Region
2024-01-05	100	North
2024-01-15	150	North
2024-02-10	200	South
2024-02-20	250	South
2024-03-05	300	East
2024-03-25	350	East
2024-04-10	400	West
2024-04-20	450	West
2024-05-05	500	North
2024-05-15	550	North
2024-06-10	600	South
2024-06-20	650	South

1

Step 1: Load the sales data into Power BI and ensure the Date column is recognized as a date type.

Import the data and check the data type of the Date column in the data model.

Expected Result

Date column is set as Date type, enabling time intelligence functions.

2

Step 2: Create a measure for Year-To-Date sales using the TOTALYTD function.

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

Expected Result

A measure that calculates the sum of sales from the start of the year up to each date.

3

Step 3: Create a measure for Quarter-To-Date sales using the TOTALQTD function.

Total Sales QTD = TOTALQTD(SUM('Sales'[Sales Amount]), 'Sales'[Date])

Expected Result

A measure that calculates the sum of sales from the start of the quarter up to each date.

4

Step 4: Create a measure for Month-To-Date sales using the TOTALMTD function.

Total Sales MTD = TOTALMTD(SUM('Sales'[Sales Amount]), 'Sales'[Date])

Expected Result

A measure that calculates the sum of sales from the start of the month up to each date.

5

Step 5: Add a line chart visual to the report canvas. Set the Axis to the Date column and add the three measures as Values.

Axis: 'Sales'[Date] Values: Total Sales YTD, Total Sales QTD, Total Sales MTD

Expected Result

A line chart showing three lines representing YTD, QTD, and MTD sales trends over time.

6

Step 6: Format the line chart for clarity: add a legend, set date axis to continuous, and ensure colors are distinct.

Use the Format pane to enable legend, set X-axis type to continuous, and assign distinct colors to each measure line.

Expected Result

A clear, easy-to-read line chart with labeled lines for each sales measure.

Final Result

Date       | Total Sales YTD | Total Sales QTD | Total Sales MTD
-------------------------------------------------------------
2024-01-05 | 100             | 100             | 100
2024-01-15 | 250             | 250             | 250
2024-02-10 | 450             | 450             | 200
2024-02-20 | 700             | 700             | 450
2024-03-05 | 1000            | 1000            | 300
2024-03-25 | 1350            | 1350            | 650
2024-04-10 | 1750            | 400             | 400
2024-04-20 | 2200            | 850             | 850
2024-05-05 | 2700            | 1350            | 500
2024-05-15 | 3250            | 1900            | 1050
2024-06-10 | 3850            | 600             | 600
2024-06-20 | 4500            | 1250            | 1250

✓Sales steadily increase over the year as shown by the YTD measure.

✓Quarterly sales show spikes at the start of each quarter.

✓Monthly sales highlight short-term sales performance within each month.

Bonus Challenge

Create a slicer to filter the sales data by Region and observe how the YTD, QTD, and MTD measures update accordingly.

Show Hint

Add a slicer visual with the Region column and ensure your measures respect the filter context automatically.