DATESYTD and cumulative totals in Power BI - Step-by-Step Guide

Introduction

This feature helps you show running totals of sales or other values from the start of the year up to each date. It solves the problem of seeing how numbers add up over time within the current year.

When you want to show how sales grow month by month from January to today.

When your report needs a line chart that displays cumulative revenue for the year.

When you want to compare year-to-date totals across different years.

When you need a table that shows running totals for expenses by date.

When you want to highlight progress towards yearly goals as time passes.

Steps

Step 1: Open your Power BI Desktop file

- Power BI Desktop main window

Your report and data model are visible

Step 2: Click on the Modeling tab

- Ribbon at the top

Modeling options appear

Step 3: Click New measure

- Modeling tab

A formula bar appears to enter DAX

Step 4: Type the DAX formula using DATESYTD, for example: Total Sales YTD = CALCULATE(SUM(Sales[Amount]), DATESYTD('Calendar'[Date]))

- Formula bar

A new measure named Total Sales YTD is created

Step 5: Add a line chart visual to the report canvas

- Visualizations pane

An empty line chart appears on the canvas

Step 6: Drag the Date field to the Axis area

- Visualizations pane under Axis

Dates appear on the horizontal axis of the chart

Step 7: Drag the Total Sales YTD measure to the Values area

- Visualizations pane under Values

The line chart shows cumulative sales from the start of the year to each date

Before vs After

Before

A table shows daily sales amounts with no running totals, e.g., Jan 1 = 100, Jan 2 = 150, Jan 3 = 120

After

A line chart shows cumulative sales: Jan 1 = 100, Jan 2 = 250, Jan 3 = 370, adding each day's sales to the total so far this year

Settings Reference

Date column

📍 Fields pane when creating DAX formulas

Defines the dates over which the year-to-date calculation runs

Default: None

Fiscal year end date (optional)

📍 DATESYTD function parameters in DAX formula

Adjusts the year-to-date calculation to match fiscal year instead of calendar year

Default: December 31

Common Mistakes

Using a date column without a proper calendar table

DATESYTD needs a continuous date column to calculate correctly

Create or use a calendar table with all dates and mark it as a date table in Power BI

Not using CALCULATE with DATESYTD

Without CALCULATE, the filter context does not apply and the measure won't show cumulative totals

Wrap SUM inside CALCULATE with DATESYTD as filter, e.g., CALCULATE(SUM(Sales[Amount]), DATESYTD('Calendar'[Date]))

Summary

DATESYTD helps create running totals from the start of the year to each date.

Use it inside CALCULATE with a proper date column for correct results.

It is useful for showing progress and trends over the year in reports.

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