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.