DATESYTD and cumulative totals 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 the year-to-date (YTD) sales totals for each month in the current year, along with a cumulative total that grows as the months progress.

📊 Data: You have monthly sales data including the date of the sale and the sales amount.

🎯 Deliverable: Create a Power BI report showing a table with Month, Sales for that month, YTD Sales, and Cumulative Sales totals.

Sample Data

Date	Sales Amount
2024-01-15	1000
2024-01-28	1500
2024-02-10	2000
2024-02-20	1800
2024-03-05	2200
2024-03-25	1700
2024-04-15	2100
2024-04-28	1900
2024-05-10	2300
2024-05-20	2500
2024-06-05	2400
2024-06-25	2600

1

Step 1: Create a Date table with continuous dates covering the sales data period.

Use Power BI's 'New Table' feature and enter: Date = CALENDAR(DATE(2024,1,1), DATE(2024,12,31))

Expected Result

A Date table with all dates from January 1, 2024 to December 31, 2024.

2

Step 2: Mark the Date table as a Date table in Power BI.

Select the Date table, then under 'Table tools', click 'Mark as Date Table' and select the Date column.

Expected Result

Power BI recognizes the Date table for time intelligence functions.

3

Step 3: Create a relationship between the Sales data Date column and the Date table Date column.

In Model view, drag Sales[Date] to Date[Date] to create a one-to-many relationship.

Expected Result

Sales data is linked to the Date table for filtering and calculations.

4

Step 4: Create a measure to calculate total sales per month.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

Measure that sums sales amounts.

5

Step 5: Create a measure to calculate Year-To-Date (YTD) sales using DATESYTD.

YTD Sales = CALCULATE([Total Sales], DATESYTD('Date'[Date]))

Expected Result

Measure that shows total sales from the start of the year up to the selected date.

6

Step 6: Create a measure to calculate cumulative sales by month.

Cumulative Sales = CALCULATE([Total Sales], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))

Expected Result

Measure that sums sales from the earliest date up to the current date in context.

7

Step 7: Build a table visual in Power BI with Month, Total Sales, YTD Sales, and Cumulative Sales.

Add 'Date'[Month] (formatted as MMM YYYY) to Rows, then add the three measures to Values.

Expected Result

Table shows each month with its sales, YTD sales, and cumulative sales.

Final Result

Month      | Total Sales | YTD Sales | Cumulative Sales
--------------------------------------------------------
Jan 2024   | 2500        | 2500      | 2500
Feb 2024   | 3800        | 6300      | 6300
Mar 2024   | 3900        | 10200     | 10200
Apr 2024   | 4000        | 14200     | 14200
May 2024   | 4800        | 19000     | 19000
Jun 2024   | 5000        | 24000     | 24000

✓Sales increase steadily each month from January to June.

✓YTD Sales and Cumulative Sales match because the data is shown monthly in order.

✓The DATESYTD function correctly accumulates sales from the start of the year to each month.

Bonus Challenge

Create a dynamic YTD sales measure that works correctly even if the fiscal year starts in a month other than January.

Show Hint

Use the DATESYTD function with the optional 'year_end_date' parameter to specify the fiscal year end.