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.

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