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.

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