DATESYTD and cumulative totals in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the year-to-date sales total using DATESYTD.

Power BI

Total Sales YTD = CALCULATE(SUM(Sales[Amount]), [1](Calendar[Date]))

Drag options to blanks, or click blank then click option'

ADATESMTD

BTOTALYTD

CDATESYTD

DDATESQTD

Attempts:

3 left

💡 Hint

Common Mistakes

Using TOTALYTD instead of DATESYTD inside CALCULATE.

Using DATESMTD which is for month-to-date, not year-to-date.

The DATESYTD function returns a set of dates from the start of the year to the current date, which is perfect for calculating year-to-date totals.

2fill in blank

medium

Complete the code to create a cumulative total measure that sums sales up to the current date.

Power BI

Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar), Calendar[Date] <= [1]))

Drag options to blanks, or click blank then click option'

AMAX(Calendar[Date])

BMAX(Sales[Date])

CMIN(Calendar[Date])

DTODAY()

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX(Sales[Date]) which may not be related to the calendar table.

Using MIN(Calendar[Date]) which gives the earliest date, not the current.

Using MAX(Calendar[Date]) gets the current date in the filter context to sum all sales up to that date.

3fill in blank

hard

Fix the error in this cumulative total measure by completing the blank.

Power BI

Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL([1]), Calendar[Date] <= MAX(Calendar[Date])))

Drag options to blanks, or click blank then click option'

ACalendar

BSales

CCustomers

DProducts

Attempts:

3 left

💡 Hint

Common Mistakes

Using ALL(Sales) which does not contain the Date column.

Using ALL(Customers) or ALL(Products) which are unrelated to dates.

The ALL(Calendar) removes filters from the Calendar table so the FILTER can compare all dates properly.

4fill in blank

hard

Fill both blanks to calculate the year-to-date cumulative sales correctly.

Power BI

YTD Cumulative Sales = CALCULATE(SUM(Sales[Amount]), [1](Calendar[Date]), FILTER(ALL(Calendar), Calendar[Date] <= [2]))

Drag options to blanks, or click blank then click option'

ADATESYTD

BTOTALYTD

CMAX(Calendar[Date])

DMIN(Calendar[Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using TOTALYTD instead of DATESYTD inside CALCULATE.

Using MIN(Calendar[Date]) which filters incorrectly.

Use DATESYTD to get year-to-date dates and MAX(Calendar[Date]) to filter dates up to the current date.

5fill in blank

hard

Fill all three blanks to create a cumulative total measure that resets each year.

Power BI

Cumulative Sales YTD = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Calendar), Calendar[Date] <= [1] && YEAR(Calendar[Date]) = [2]), VALUES(Calendar[[3]]))

Drag options to blanks, or click blank then click option'

AMAX(Calendar[Date])

BYEAR(MAX(Calendar[Date]))

CYear

DMonth

Attempts:

3 left

💡 Hint

Common Mistakes

Using Month instead of Year in the last blank.

Using TODAY() instead of MAX(Calendar[Date]) for the date.

The measure sums sales up to the max date (MAX(Calendar[Date])) within the current year (YEAR(MAX(Calendar[Date]))) and filters by the Year column (Year).