EPEnglishPreps Log InFree Sign Up
Home › Academic Composition › Other Compositions

Course (Part 757)

Other Compositions · English · 4 Dec 2025

🎯 Other Compositions🌐 English📅 4 Dec 2025📖 Course 32 of 683
📚 All Other Compositions compositions
🧠 Test Your Skill10 questions from this composition
Other CompositionsCourse (Part 757)

Course: Time Series Analysis in Power BI

In data analytics, time series data is a sequence of observations recorded at regular time intervals that enables analysts to detect trends, seasonality, and anomalies. Mastering time series is essential for forecasting future values, monitoring KPIs, and making informed business decisions. In Power BI, Data Analysis Expressions (DAX) provides a compact and expressive language to shape time-based models, compute measures, and run queries. A recommended first step is to create a dedicated date or calendar table (for example, using CALENDAR(MIN(...), MAX(...))) so that every time-intelligence function has a consistent temporal backbone. Once a calendar table exists, SUMMARIZECOLUMNS can be used to group metrics by month, year, or other dimensions while applying filters such as specific product categories. Because DAX queries do not inherit the visual sort order, ORDER BY clauses must be included in queries when chronological order is required. The START AT clause is a practical tool for pagination or focused inspection of large ordered tables, such as beginning a list of sales orders at a particular ID. Window functions and time-intelligence patterns — including running totals, moving averages, and RANKX-based rankings — let analysts compute contextual metrics across related rows. Measures such as rolling averages (via DATESINPERIOD) and year-over-year comparisons (via SAMEPERIODLASTYEAR) are especially useful for smoothing volatility and spotlighting growth. Advanced scenarios like hindcasting, forecasting, or computing CAGR/CMGR require careful selection of windows and smoothing parameters. Throughout, DIVIDE and CALCULATE are frequent helpers: DIVIDE to avoid divide-by-zero errors and CALCULATE to change filter context for the calculation. Ultimately, combining a well-modeled date dimension with robust DAX measures empowers concise, repeatable, and interpretable temporal analyses in Power BI.

Lesson

Topic: Introduction to Time Series Data & DAX in Power BI

Learning Outcomes

  • Understand what time series data is and why it matters in analytics.
  • Create a calendar (date) table using DAX as a foundation for time intelligence.
  • Summarize time series data using SUMMARIZECOLUMNS and include measures safely with DIVIDE.
  • Control result ordering in DAX queries with ORDER BY and paginate with START AT.
  • Apply window-function patterns: running totals, moving averages, and ranking.
  • Build YoY, CAGR, and rolling average measures for forecasting and trend analysis.

Conceptual Explanation

Time series data is indexed by time and analyzed to reveal trends, seasonal patterns, and anomalies. Power BI relies on a proper date dimension (calendar table) to perform accurate time intelligence, which is commonly created with the CALENDAR function in DAX. Once the date table exists, grouping and aggregation across time windows is typically implemented with functions such as SUMMARIZECOLUMNS, CALCULATE, DATESINPERIOD, SAMEPERIODLASTYEAR, and DATESINPERIOD. For safe arithmetic use DIVIDE to prevent divide-by-zero issues. DAX query results do not inherit Power BI visual sort orders; therefore, explicit ORDER BY clauses are needed for chronological displays. For large result sets you can paginate or start from a specific row using START AT in a DAX query. Window-function patterns (running total via FILTER / ALLSELECTED, moving average via AVERAGEX + DATESINPERIOD, and ranking via RANKX) provide row-relative calculations that are valuable for operational and strategic analytics. Forecasting uses these calculated measures and time windows as a basis; hindcasting uses the same functions to validate models against historical data. Finally, prepare to iterate: test measures, visualize side-by-side with raw values, and refine smoothing parameters (alpha) or window lengths to improve predictive quality.

Common DAX snippets (examples)

Purpose DAX Example Notes
Create calendar table 
Dim_Calendar = CALENDAR(MIN('Fact_StrategyPlan'[Datekey]), MAX('Fact_StrategyPlan'[Datekey]))
 Generates continuous date range based on fact table
Summarize by month & category 
EVALUATE
SUMMARIZECOLUMNS(
  'Date'[Month Name],
  'Date'[Month of Year],
  'Product'[Category],
  "Orders", [Orders],
  "Avg Profit per Order", DIVIDE([Total Sales Profit],[Orders])
)
 Use DIVIDE for safe division
Order results by month 
ORDER BY 'Date'[Month of Year] ASC
 Explicit ordering in DAX query
Start at a specific row 
START AT "SO43661"
 Useful for pagination/focused inspection

Bilingual (EN → বাংলা)

English: Create a calendar table with CALENDAR(MIN(...), MAX(...)) to enable time intelligence.

বাংলা: টাইম ইন্টেলিজেন্স সক্ষম করার জন্য CALENDAR(MIN(...), MAX(...)) ব্যবহার করে একটি ক্যালেন্ডার টেবিল তৈরী করুন।

English: Use SUMMARIZECOLUMNS to group by time periods and calculate measures.

বাংলা: সময়ভিত্তিক গ্রুপিং এবং মেজার গণনার জন্য SUMMARIZECOLUMNS ব্যবহার করুন।

English: ORDER BY must be added in DAX queries to preserve chronological order.

বাংলা: ক্রোনোলজিকাল অর্ডার বজায় রাখতে DAX কুয়েরিতে ORDER BY যুক্ত করতে হবে।

Important Notes

  • Always create and mark a proper Date table in Power BI to enable time intelligence functions.
  • SUMMARIZECOLUMNS is efficient for grouping in DAX queries, but measures must be defined in the model or passed explicitly.
  • DAX queries ignore visual sort settings; use ORDER BY in queries.
  • Use DIVIDE for safe division operations to avoid errors from zero denominators.
  • Window functions in DAX are implemented via CALCULATE + FILTER, DATESINPERIOD, AVERAGEX and RANKX patterns — not a single SQL-like window keyword.

Exercises

True / False

  1. Creating a calendar table is optional for time intelligence in Power BI.
    2. Answer

    False — A dedicated calendar table is recommended and often required for accurate time intelligence calculations (e.g., SAMEPERIODLASTYEAR, DATESINPERIOD).

  2. DAX queries automatically inherit the sort order defined in Power BI visuals.
    3. Answer

    False — DAX queries do not inherit visual sort order; you must include ORDER BY in the query if specific ordering is required.

  3. DIVIDE handles division by zero more safely than the “/” operator in DAX.
    4. Answer

    True — DIVIDE returns an alternate result or BLANK when the denominator is zero, avoiding errors.

Fill in the Blanks

  1. To create a full list of dates between the earliest and latest date in a fact table we use the DAX function ________.
    2. Answer

    CALENDAR

  2. The function ________ is commonly used to compute moving averages over a specified period.
    3. Answer

    AVERAGEX together with DATESINPERIOD (e.g., AVERAGEX(DATESINPERIOD(...), CALCULATE(SUM(...))))

  3. To compute year-over-year comparisons in DAX you can use the ________ function.
    4. Answer

    SAMEPERIODLASTYEAR

Multiple Choice Questions (MCQ)

  1. Which DAX function is best for grouping in a query that can include filters and measures?
    1. SUMMARIZE
    2. SUMMARIZECOLUMNS
    3. GROUPBY
    4. ROLLUP
    Answer

    b) SUMMARIZECOLUMNS — It is optimized for queries and allows explicit measures and filters.

  2. Which pattern is commonly used to compute a running total in DAX?
    1. SUMX with EARLIER only
    2. CALCULATE(SUM(...), FILTER(ALLSELECTED('Date'), 'Date' <= MAX('Date')))
    3. DIVIDE(SUM(...), COUNT(...))
    4. RANKX with DENSE
    Answer

    b) CALCULATE with FILTER and ALLSELECTED — this pattern accumulates values up to the current row's date.

Matching

Match the DAX function on the left with its primary purpose on the right.

Function Purpose
1. CALENDAR A. Compute rankings
2. RANKX B. Create date dimension
3. DATESINPERIOD C. Provide a sliding window of dates
4. DIVIDE D. Avoid divide-by-zero
Answer

1 → B, 2 → A, 3 → C, 4 → D

Descriptive

  1. Explain why a calendar table is recommended and describe at least three columns you would include in it.
    2. Answer

    A calendar table standardizes time contexts across the model, enabling reliable time intelligence calculations and consistent slicing. Include: (1) Date (the key), (2) Year, (3) MonthNumber, (4) MonthName, (5) Quarter, and optionally fiscal year and weekday flags. Mark it as the Date table in Power BI so time-intelligence functions operate correctly.

  2. Describe how you would implement a 3-month rolling average of Sales in DAX.
    3. Answer

    Create a measure such as: 

    Rolling Avg Sales =
CALCULATE(
  AVERAGE('Fact'[SalesAmount]),
  DATESINPERIOD(Dim_Calendar[Date], LASTDATE(Dim_Calendar[Date]), -3, MONTH)
)
    This uses DATESINPERIOD to produce a 3-month date window ending at the current context date and averages sales over that window.

Creative

  1. Design a brief plan (3–4 steps) for validating a forecasting model for monthly sales using Power BI and DAX.
    2. Answer

    Plan:

    1. Create and validate the Date table and relationships to your sales fact.
    2. Compute historical baseline measures: monthly sales, moving averages, and trend lines.
    3. Build the forecast measure (e.g., moving average or exponential smoothing) and compare forecasted values to holdout historical months (hindcasting).
    4. Evaluate accuracy with MAPE or RMSE measures and iterate smoothing parameters or model window sizes.

Activities, Vocabulary, Homework, Practice Task

Activities

  1. Activity 1: Create a Dim_Calendar table using your dataset and add Year, MonthNumber, MonthName, and FiscalYear columns.
    2. Answer

    Example DAX to start: 

    Dim_Calendar =
ADDCOLUMNS(
  CALENDAR(MIN('Fact'[Date]), MAX('Fact'[Date])),
  "Year", YEAR([Date]),
  "MonthNumber", MONTH([Date]),
  "MonthName", FORMAT([Date],"MMMM"),
  "Quarter", "Q" & FORMAT([Date],"Q")
)

  2. Activity 2: Write a DAX measure for average profit per order using DIVIDE.
    3. Answer

    Measure example: 

    Avg Profit per Order = DIVIDE([Total Sales Profit], [Orders], 0)
    This returns 0 if Orders is 0 to avoid errors.

Vocabulary

  • Time series
    • Answer

    Sequence of data points indexed in time order (e.g., daily sales).

  • Calendar table / Date table
    • Answer

    A dedicated table listing dates and date attributes used for time intelligence in Power BI.

  • Window function pattern
    • Answer

    A DAX technique (CALCULATE + FILTER/ALL/ALLSELECTED + time functions) that computes row-relative aggregates such as running totals, moving averages, and ranks.

Homework

  1. Implement three measures in your dataset: Rolling 3-month average sales, Running Total of quantity, and YoY % change of sales. Document DAX and results.
    2. Answer

    Suggested measures (templates): 

    Rolling 3M = CALCULATE( AVERAGE('Fact'[SalesAmount]), DATESINPERIOD(Dim_Calendar[Date], LASTDATE(Dim_Calendar[Date]), -3, MONTH) )
Running Total = CALCULATE( SUM('Fact'[Quantity]), FILTER( ALLSELECTED(Dim_Calendar[Date]), Dim_Calendar[Date] <= MAX(Dim_Calendar[Date]) ) )
YoY % = VAR Cur = [Total Sales]
VAR Prev = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dim_Calendar[Date]))
RETURN DIVIDE(Cur - Prev, Prev)

Practice Task

Create a DAX query that summarizes monthly orders and average profit per order for the "Clothing" category and orders results by month number.

Answer

Example query: 

EVALUATE
SUMMARIZECOLUMNS(
  'Dim_Calendar'[Month Name],
  'Dim_Calendar'[Month of Year],
  'Product'[Category],
  FILTER( VALUES('Product'[Category]), 'Product'[Category] = "Clothing" ),
  "Orders", [Orders],
  "Avg Profit per Order", DIVIDE([Total Sales Profit],[Orders],0)
)
ORDER BY 'Dim_Calendar'[Month of Year] ASC

End of lecture sheet. Practice DAX iteratively: write measures, visualize side-by-side with raw numbers, and validate with holdout/hindcast tests.

Similar compositions

Course (Part 793)Other CompositionsCourse (Part 792)Other CompositionsCourse (Part 790)Other CompositionsCourse (Part 789)Other CompositionsCourse (Part 784)Other CompositionsCourse (Part 783)Other Compositions
🎓 Want more writing practice?Make academic composition sheets for any class, subject and chapter on AlphaGenEdu - free.
Create free account →