Why time-based analysis drives decisions in Power BI - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate total sales for the current year.

Power BI

Total Sales Current Year = CALCULATE(SUM(Sales[Amount]), YEAR(Sales[Date]) = [1])

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

ADAY(TODAY())

BMONTH(TODAY())

CYEAR(TODAY())

DYEAR(Sales[Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using MONTH(TODAY()) instead of YEAR(TODAY())

Filtering by DAY(TODAY())

We use YEAR(TODAY()) to get the current year for filtering sales.

2fill in blank

medium

Complete the code to calculate sales growth compared to the previous year.

Power BI

Sales Growth = DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), [1](Sales[Date])), CALCULATE(SUM(Sales[Amount]), [1](Sales[Date])))

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

ASAMEPERIODLASTYEAR

BDATESYTD

CNEXTYEAR

DPREVIOUSMONTH

Attempts:

3 left

💡 Hint

Common Mistakes

Using PREVIOUSMONTH instead of SAMEPERIODLASTYEAR

Using NEXTYEAR which is future dates

SAMEPERIODLASTYEAR returns the same period last year, which is needed to compare sales.

3fill in blank

hard

Fix the error in the code to calculate year-to-date sales correctly.

Power BI

YTD Sales = TOTALYTD(SUM(Sales[Amount]), Sales[[1]])

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

ADate

BAmount

CYear

DMonth

Attempts:

3 left

💡 Hint

Common Mistakes

Using Amount or Year columns instead of Date

Using Month which is not a full date

The TOTALYTD function requires a date column to calculate year-to-date totals.

4fill in blank

hard

Fill both blanks to create a measure that calculates average sales per month for the current year.

Power BI

Avg Sales Per Month = DIVIDE(SUM(Sales[Amount]), CALCULATE(DISTINCTCOUNT(Sales[[1]]), YEAR(Sales[Date]) = [2]))

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

AMonth

BYEAR(TODAY())

CDate

DMONTH(TODAY())

Attempts:

3 left

💡 Hint

Common Mistakes

Using Date instead of Month for distinct count

Using MONTH(TODAY()) instead of YEAR(TODAY()) for filtering

We count distinct months in the current year using the Month column and filter by YEAR(TODAY()).

5fill in blank

hard

Fill all three blanks to create a measure that calculates the percentage of sales for the current month compared to the total sales of the year.

Power BI

Monthly Sales % = DIVIDE(CALCULATE(SUM(Sales[Amount]), YEAR(Sales[[3]]) = [1], MONTH(Sales[[3]]) = [2]), CALCULATE(SUM(Sales[Amount]), YEAR(Sales[[3]]) = [1])) * 100

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

AYEAR(TODAY())

BMONTH(TODAY())

CDate

DAmount

Attempts:

3 left

💡 Hint

Common Mistakes

Using Amount instead of Date for filtering

Mixing up month and year filters

The measure divides current month sales by total sales in the current year. We filter by YEAR(TODAY()) and use MONTH(TODAY()) to identify the current month. The date column is used for filtering.