DATESYTD and cumulative totals in Power BI - Dashboard Guide

Dashboard Mode - DATESYTD and cumulative totals

Dashboard Goal

Understand how sales accumulate over the year up to each date using the DATESYTD function and show cumulative totals.

Sample Data

Date	Sales
2024-01-05	100
2024-02-10	200
2024-03-15	150
2024-04-20	300
2024-05-25	250
2024-06-30	400
2024-07-10	350

Dashboard Components

• KPI Card: Total Sales YTD
  Formula:
  Total Sales YTD = CALCULATE(SUM(Sales[Sales]), DATESYTD(Sales[Date]))
  Result:
  Sum of sales from Jan 1 to latest date in data (2024-07-10) = 100+200+150+300+250+400+350 = 1750
• Line Chart: Cumulative Sales Over Time
  Formula for cumulative total measure:
  Cumulative Sales = CALCULATE(SUM(Sales[Sales]), FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date])))
  Shows sales adding up day by day from start of year to each date.
• Table: Sales by Date with Cumulative Total
  Columns: Date, Sales, Cumulative Sales (using above measure)
  Shows each date's sales and running total.

Dashboard Layout

+----------------------+-------------------------+
|      Total Sales YTD  |   Cumulative Sales Chart |
|        (KPI Card)     |      (Line Chart)        |
+----------------------+-------------------------+
|           Sales by Date with Cumulative Total Table           |
+--------------------------------------------------------------+

Interactivity

A date slicer allows selecting a date range. When the user changes the date range, the KPI card, line chart, and table update to show sales and cumulative totals only for the selected dates within the year.

Self Check

If you add a filter to show only sales up to 2024-04-20, what is the Total Sales YTD value?

Answer: Sum sales from 2024-01-05 to 2024-04-20 = 100 + 200 + 150 + 300 = 750

Key Result

Dashboard showing year-to-date sales total and cumulative sales over time with date filtering.