Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Time-Based Business Calculations

Time-based business calculations help organizations understand how performance changes over days, months, quarters, and years. In Power BI, DAX time intelligence functions can compare sales across periods, calculate year-to-date totals, and measure growth over time.

These calculations are useful for financial reporting, sales analysis, budgeting, inventory planning, and management dashboards.

Learning Objectives

  • Understand the role of a date table in time intelligence.
  • Create year-to-date and month-to-date measures.
  • Compare current-period sales with previous-period sales.
  • Calculate year-over-year growth and percentage change.
  • Understand common time intelligence functions.
  • Display time-based calculations in Power BI visuals.

1. Why Use Time-Based Calculations?

A total sales figure provides only one view of business performance. Time-based calculations help answer questions such as:

  • How much revenue has been earned so far this year?
  • Did sales increase compared with the previous month?
  • How does this year's performance compare with last year?
  • What is the percentage change in sales between two periods?
  • How are sales progressing against a monthly or yearly target?
Time Intelligence Workflow Date Table Continuous dates DAX Calculation YTD, PY, Growth Business Insight Period comparison A reliable date table supports accurate time-based analysis.

2. Create a Date Table

A date table provides a consistent calendar for time-based calculations. It should contain one row per date, without duplicate or missing dates within the intended calendar range.

Create a date table using the following DAX formula:

DateTable =
CALENDAR(
    DATE(2020, 1, 1),
    DATE(2030, 12, 31)
)

Adjust the start and end dates to match the reporting requirements of your organization.

Add Calendar Columns

Year =
YEAR(DateTable[Date])
Month Number =
MONTH(DateTable[Date])
Month Name =
FORMAT(DateTable[Date], "MMMM")
Quarter =
"Q" & FORMAT(DateTable[Date], "Q")
Best Practice: Sort Month Name by Month Number so months appear in calendar order instead of alphabetical order.

3. Configure the Date Table

  1. Create the DateTable in your Power BI model.
  2. Make sure the Date column contains unique date values.
  3. Set the Date column data type to Date.
  4. Use the available Mark as date table option and select the Date column.
  5. Create a relationship between DateTable[Date] and the date column in the fact table.
  6. Use fields from DateTable for calendar-based report filters and visuals.
Important: The fact table's date column and the date table column must have compatible data types. If the fact column contains timestamps, create or use a date-only column for the relationship.

4. Create a Base Sales Measure

Time intelligence calculations typically build on a base measure. Assume the Sales table contains a numeric SalesAmount column and a valid relationship to DateTable.

Total Sales =
SUM(Sales[SalesAmount])

This measure returns the total sales for the current filter context. When used with DateTable fields, it can calculate sales for selected dates, months, quarters, or years.

5. Year-to-Date (YTD) Sales

Year-to-date sales calculates the total from the beginning of the current year through the last date in the current evaluation context.

Sales YTD =
TOTALYTD(
    [Total Sales],
    DateTable[Date]
)

For example, when the report is filtered to a date in June, the measure calculates sales from January 1 through the relevant date in June.

6. Month-to-Date (MTD) Sales

Month-to-date sales calculates sales from the beginning of the current month through the last date in the current evaluation context.

Sales MTD =
TOTALMTD(
    [Total Sales],
    DateTable[Date]
)

Use this measure to monitor progress within a month. When comparing MTD results across different dates, make sure the compared periods cover equivalent numbers of days when that matters to the analysis.

7. Quarter-to-Date (QTD) Sales

Quarter-to-date sales accumulates sales from the beginning of the current quarter through the relevant date.

Sales QTD =
TOTALQTD(
    [Total Sales],
    DateTable[Date]
)

QTD calculations are useful for quarterly business reviews, management reporting, and progress tracking against quarterly goals.

Common Accumulation Periods MTD Start of month to current date Monthly progress QTD Start of quarter to current date Quarterly progress YTD Start of year to current date Annual progress

8. Previous Year Sales

To compare sales with the same period in the previous year, use SAMEPERIODLASTYEAR with the date table.

Sales Previous Year =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(DateTable[Date])
)

When the report is filtered to a date range, this measure evaluates sales for the corresponding dates in the previous year, subject to the date context and the model.

9. Year-over-Year (YoY) Change

Year-over-year change measures the difference between current-period sales and sales for the comparable period in the previous year.

Sales YoY Change =
[Total Sales] - [Sales Previous Year]

To calculate the percentage change, divide the difference by the previous year's sales.

Sales YoY % =
DIVIDE(
    [Sales YoY Change],
    [Sales Previous Year]
)

Format Sales YoY % as a percentage. A positive result indicates growth; a negative result indicates a decline.

10. Previous Month Sales

Use DATEADD to shift the selected date context back by one month.

Sales Previous Month =
CALCULATE(
    [Total Sales],
    DATEADD(DateTable[Date], -1, MONTH)
)

Compare current-period sales with the previous month using the following measure.

Sales MoM Change =
[Total Sales] - [Sales Previous Month]
Sales MoM % =
DIVIDE(
    [Sales MoM Change],
    [Sales Previous Month]
)
Important: Period comparisons depend on the selected dates and date-table configuration. Validate month-end behavior, incomplete periods, and missing data before interpreting results.

11. Running Total

A running total accumulates values over time up to the current date in the report context.

Running Total Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(DateTable[Date]),
        DateTable[Date] <= MAX(DateTable[Date])
    )
)

This example removes the existing filter from DateTable[Date] and includes dates up to the current maximum date. Other filters, such as product or region, can continue to affect the result.

Illustrative Running Total Jan Feb Mar Apr May

12. Business Example

Assume a company wants to compare monthly sales with the previous year and monitor progress toward its annual goal.

Metric Purpose Suggested Visual
Total Sales Shows sales for the selected period. Card
Sales YTD Shows accumulated sales for the year. Card or line chart
Sales Previous Year Provides a comparable prior-year value. Card or table
Sales YoY % Shows growth or decline compared with the previous year. Card or KPI visual
Sales MoM % Shows change compared with the previous month. Card or column chart
Running Total Sales Shows cumulative sales over time. Line chart

13. Common Mistakes

Missing or Incorrect Date Table

Use a complete date table with unique dates and a valid relationship to the fact table.

Incorrect Date Data Type

Confirm that the date columns use compatible data types and that timestamps are handled correctly.

Comparing Incomplete Periods

Comparing a partial current month with a full previous month can produce misleading conclusions. Compare equivalent periods when appropriate.

Dividing by Zero

Use DIVIDE when calculating growth percentages to handle zero or blank denominators safely.

Incorrect Month Sorting

Sort month names by a numeric month column to maintain calendar order.

14. Student Practice Tasks

Task 1: Create a DateTable covering your reporting period.

Task 2: Add Year, Month Number, Month Name, and Quarter columns.

Task 3: Create a Total Sales measure.

Task 4: Create Sales YTD, Sales MTD, and Sales QTD measures.

Task 5: Calculate Sales Previous Year using SAMEPERIODLASTYEAR.

Task 6: Create Sales YoY Change and Sales YoY %.

Task 7: Calculate Sales Previous Month and Sales MoM %.

Task 8: Create a running total and display it in a line chart.

Task 9: Add a date slicer and test how each measure responds.

15. Knowledge Check

Question 1: What does YTD mean?

Answer: Year-to-date.

Question 2: Which function can calculate a year-to-date total?

Answer: TOTALYTD().

Question 3: Which function can compare dates with the same period last year?

Answer: SAMEPERIODLASTYEAR().

Question 4: How is year-over-year percentage change calculated?

Answer: Current-period sales minus previous-year sales, divided by previous-year sales.

Question 5: Why is a date table important?

Answer: It provides a consistent calendar for filtering, grouping, and time intelligence calculations.

Summary

In this lesson, you learned how to create a date table and use DAX time intelligence functions for year-to-date, month-to-date, quarter-to-date, previous-year, and previous-month calculations. You also practised calculating year-over-year change, month-over-month change, and running totals.

Accurate time-based calculations depend on a reliable date table, correct relationships, compatible date data types, and appropriate comparison periods. Validate your measures with sample data before using them in business dashboards.