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.