Why time-based analysis drives decisions in Power BI - Dashboard Impact

Dashboard Mode - Why time-based analysis drives decisions

Business Question

How do sales change over time, and which months show growth or decline? This helps decide when to boost marketing or stock inventory.

Sample Data

Month	Sales
January	100
February	150
March	130
April	170
May	160
June	180

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUM(SalesData[Sales])
  Result: 890
• Line Chart: Sales Over Months
  X-axis: Month
  Y-axis: Sales
  Shows sales trend from January to June.
• Bar Chart: Month-over-Month Sales Change
  Formula: Sales Change = SUM(SalesData[Sales]) - CALCULATE(SUM(SalesData[Sales]), PREVIOUSMONTH(SalesData[Month]))
  Shows increase or decrease compared to previous month.

Dashboard Layout

+----------------------+-----------------------+
|      Total Sales      |   Sales Over Months   |
|       (KPI Card)      |      (Line Chart)     |
+----------------------+-----------------------+
|    Month-over-Month   |                       |
|     Sales Change      |                       |
|      (Bar Chart)      |                       |
+----------------------+-----------------------+

Interactivity

A slicer for selecting months lets you focus on specific periods. When you pick months, all visuals update to show data only for those months. This helps spot trends or dips in chosen time frames.

Self Check

If you add a filter to show only March to June, which components update and what happens?

• All components update to show data from March to June only.
• Total Sales KPI recalculates sum for these months (130+170+160+180 = 640).
• Line chart redraws sales trend for March to June.
• Bar chart shows month-over-month changes only within March to June.

Key Result

Dashboard shows total sales and monthly sales trends to help understand time-based sales changes.

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