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.

Practice

1. Why is time-based analysis important in business intelligence?

easy

A. It helps identify trends and patterns over time.

B. It only shows data for the current day.

C. It ignores seasonal changes in data.

D. It removes the need for data visualization.

Solution

1. Step 1: Understand the role of time in data

   Time-based analysis looks at data across different periods to find changes and trends.

2. Step 2: Recognize the benefit of trends and patterns

   Identifying trends helps businesses make better decisions by understanding how things change over time.

3. Final Answer:

   It helps identify trends and patterns over time. -> Option A

4. Quick Check:

   Time-based analysis = trends and patterns [OK]

Hint: Think about how time shows changes, not just snapshots. [OK]

Common Mistakes:

• Assuming time analysis only shows current data
• Ignoring seasonal or periodic changes
• Believing it removes need for visuals

2. Which of the following is the correct DAX function to calculate Year-To-Date sales?

easy

A. YEARLYTOTAL(Sales[Amount], Dates[Date])

B. TOTALYTD(Sales[Amount], Dates[Date])

C. SUMYTD(Sales[Amount], Dates[Date])

D. YTDTOTAL(Sales[Amount], Dates[Date])

Solution

1. Step 1: Recall the standard DAX time intelligence functions

   The correct function for year-to-date calculation is TOTALYTD.

2. Step 2: Verify function syntax

   TOTALYTD requires the expression and the date column as parameters, matching the given syntax.

3. Final Answer:

   TOTALYTD(Sales[Amount], Dates[Date]) -> Option B

4. Quick Check:

   Year-To-Date = TOTALYTD [OK]

Hint: Remember: TOTALYTD is the official DAX function for YTD. [OK]

Common Mistakes:

• Using non-existent functions like SUMYTD
• Mixing function names incorrectly
• Incorrect parameter order or names

3. Given the DAX measure:
Total Sales LY = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Dates[Date]))
What does this measure calculate?

medium

A. Total sales for the previous year, same period.

B. Total sales for the current year.

C. Total sales for the next year.

D. Total sales ignoring the date filter.

Solution

1. Step 1: Understand the SAMEPERIODLASTYEAR function

   This function shifts the date context to the same period in the previous year.

2. Step 2: Analyze the CALCULATE with SUM

   CALCULATE changes the filter context to last year's same period, summing sales for that time.

3. Final Answer:

   Total sales for the previous year, same period. -> Option A

4. Quick Check:

   SAMEPERIODLASTYEAR = previous year period [OK]

Hint: SAMEPERIODLASTYEAR shifts dates to last year's matching period. [OK]

Common Mistakes:

• Thinking it calculates current year sales
• Confusing next year with last year
• Ignoring the date filter context

4. You wrote this DAX measure to calculate Month-To-Date sales:
Mtd Sales = TOTALMTD(Sales[Amount], Dates[Date])
But the result is always blank. What is the most likely cause?

medium

A. TOTALMTD requires a numeric column, not Sales[Amount].

B. The measure should use TOTALYTD instead.

C. The Dates table is not marked as a Date table.

D. Dates[Date] column contains text values.

Solution

1. Step 1: Check requirements for TOTALMTD function

   TOTALMTD requires a properly marked Date table to work correctly.

2. Step 2: Identify common issues causing blanks

   If the Dates table is not marked as a Date table, time intelligence functions return blank results.

3. Final Answer:

   The Dates table is not marked as a Date table. -> Option C

4. Quick Check:

   Mark Dates table as Date table to fix blanks [OK]

Hint: Always mark your Dates table as a Date table for time functions. [OK]

Common Mistakes:

• Using wrong function like TOTALYTD for month calculation
• Assuming numeric column type is wrong
• Ignoring Date table setup

5. You want to create a dashboard showing sales trends comparing this year and last year by month. Which approach best supports timely decision-making?

hard

A. Display a table with all sales transactions without grouping by date.

B. Show only total sales for the current year as a single number card.

C. Create a pie chart showing sales by product category without time filters.

D. Use line charts with Month on X-axis and two measures: Total Sales and Total Sales LY.

Solution

1. Step 1: Identify visualization for time comparison

   Line charts effectively show trends over time and allow easy comparison between periods.

2. Step 2: Use measures for current and last year sales

   Including Total Sales and Total Sales LY measures enables direct comparison month by month.

3. Final Answer:

   Use line charts with Month on X-axis and two measures: Total Sales and Total Sales LY. -> Option D

4. Quick Check:

   Line chart + current vs last year = best for trends [OK]

Hint: Compare periods side-by-side with line charts for clear trends. [OK]

Common Mistakes:

• Using single number cards that hide trends
• Pie charts don't show time trends
• Tables without date grouping obscure patterns