DATESYTD and cumulative totals in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table shows daily sales amounts for the first five days of January 2024.

Cell	Value
A1	Date
B1	Sales
A2	2024-01-01
B2	100
A3	2024-01-02
B3	150
A4	2024-01-03
B4	200
A5	2024-01-04
B5	120
A6	2024-01-05
B6	130

Formula Trace

TOTALYTD = CALCULATE(SUM(Sales[Sales]), DATESYTD(Sales[Date]))

Step 1: SUM(Sales[Sales])

Step 2: DATESYTD(Sales[Date])

Step 3: CALCULATE(SUM(Sales[Sales]), DATESYTD(Sales[Date]))

Cell Reference Map

     A           B
1 |   Date    | Sales  |
2 |2024-01-01|  100   |
3 |2024-01-02|  150   |
4 |2024-01-03|  200   |
5 |2024-01-04|  120   |
6 |2024-01-05|  130   |

Arrows:
- SUM references B2:B6
- DATESYTD references A2:A6

The formula uses the Sales column (B2:B6) to sum sales and the Date column (A2:A6) to filter dates year-to-date.

Result

     A           B           C
1 |   Date    | Sales  | TotalYTD |
2 |2024-01-01|  100   |   100    |
3 |2024-01-02|  150   |   250    |
4 |2024-01-03|  200   |   450    |
5 |2024-01-04|  120   |   570    |
6 |2024-01-05|  130   |   700    |

The TotalYTD column shows cumulative sales from the start of the year up to each date, calculated using DATESYTD.

Sheet Trace Quiz - 3 Questions

What does the DATESYTD function return for the date 2024-01-03?

A All dates in the entire year 2024

B All dates from 2024-01-01 to 2024-01-03

C Only the date 2024-01-03

D No dates

Key Result

DATESYTD returns all dates from the start of the year to the current date, used inside CALCULATE to get cumulative totals.

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