DATESYTD and cumulative totals in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the DATESYTD function do in Power BI?

DATESYTD returns a table of dates from the start of the year up to the current date in the filter context. It helps calculate year-to-date values.

intermediate

How do you create a cumulative total measure using DATESYTD?

You use CALCULATE with SUM of your value column and filter dates using DATESYTD to sum values from the year's start to the current date.

beginner

Write a simple DAX formula for year-to-date sales using DATESYTD.

YTD Sales = CALCULATE(SUM(Sales[Amount]), DATESYTD('Calendar'[Date]))

intermediate

Why is a proper date table important when using DATESYTD?

A proper date table ensures continuous dates and correct filtering, which is essential for accurate year-to-date calculations.

beginner

What is the difference between cumulative totals and year-to-date totals?

Cumulative totals add values over any period, while year-to-date totals specifically sum values from the start of the year to a given date.

What does the DATESYTD function return?

A A single date value for today

B A table of dates from the start of the year to the current date

C All dates in the calendar table

D Dates only for the current month

DATESYTD returns a table of dates starting from January 1st of the current year up to the current date.

Which DAX function is commonly combined with DATESYTD to calculate cumulative totals?

A CALCULATE

B SUMX

C FILTER

D ALL

CALCULATE changes the filter context and is used with DATESYTD to compute cumulative totals.

Why do you need a continuous date table for DATESYTD to work correctly?

A To filter only weekends

B To speed up report loading

C To create random dates

D To avoid missing dates and ensure accurate time calculations

A continuous date table ensures no gaps in dates, which is necessary for correct year-to-date calculations.

Which of these is a correct DAX formula for cumulative sales YTD?

A SUM(Sales[Amount]) + DATESYTD(Calendar[Date])

B DATESYTD(Sales[Amount])

C CALCULATE(SUM(Sales[Amount]), DATESYTD('Calendar'[Date]))

D CALCULATE(DATESYTD(Sales[Amount]))

The correct syntax uses CALCULATE with SUM and DATESYTD as a filter.

What does a cumulative total show in a report?

A Sum of values up to each date in the period

B Only the value for the last date

C Average of all values

D Difference between dates

Cumulative totals add values progressively up to each date, showing running totals.

Explain how you would create a year-to-date sales measure using DATESYTD in Power BI.

Think about how to sum sales from the start of the year to the current date.

Describe the difference between cumulative totals and year-to-date totals and when you might use each.

Consider how the time frame affects the totals shown.