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

DAX Date and Time Intelligence

CIIT TRANING INSTITUTE | Power BI Course

Date and time intelligence functions help analyze business performance across dates, months, quarters, and years. They allow users to compare current results with previous periods, calculate year-to-date totals, and measure changes over time.

Learning Objectives

  • Understand date tables and their importance in Power BI.
  • Use common DAX date and time intelligence functions.
  • Calculate month-to-date, quarter-to-date, and year-to-date values.
  • Compare current-period performance with previous periods.
  • Understand the requirements for reliable time intelligence calculations.

1. Understanding Date Intelligence

Date intelligence means analyzing numerical measures over a time period. For example, a sales manager may want to know total sales this year, sales during the previous year, or the difference between two years.

Analyze Sales Across Time Month Quarter Year Previous Year MTD QTD YTD YoY comparison A consistent calendar supports accurate comparisons.

2. Create a Date Table

A date table contains one row for each calendar date and can include columns such as year, month number, month name, quarter, and weekday. It helps Power BI interpret date relationships consistently.

To create a basic date table, use the following calculated table:

DateTable =
CALENDAR(
    DATE(2023, 1, 1),
    DATE(2027, 12, 31)
)

To add useful calendar columns, select the date table and create calculated columns using these expressions:

Year = YEAR(DateTable[Date])

Month Number = MONTH(DateTable[Date])

Month Name = FORMAT(DateTable[Date], "MMMM")

Quarter = "Q" & FORMAT(DateTable[Date], "Q")
DateTable Date Year Month Number Month Name Quarter 01-Jan-2025 2025 1 January Q1 02-Jan-2025 2025 1 January Q1 03-Jan-2025 2025 1 January Q1 Each date should appear once in the date column.
Best Practice: Create a relationship from DateTable[Date] to the date column in your sales fact table. Mark the table as a date table in Power BI when appropriate, and verify that the date column contains unique, continuous dates.
Important: The date table must cover the dates required by your analysis. Check the data type of the date column and make sure the relationship to the fact table is active.

3. Common Date and Time Intelligence Functions

Function Purpose Typical Use
TODAY() Returns the current date. Current-date calculations.
YEAR(date) Returns the year portion of a date. Creating a year column.
MONTH(date) Returns the month number. Sorting months in calendar order.
DATESYTD() Returns dates from the beginning of the year through the current context date. Year-to-date calculations.
DATEADD() Shifts the current date context by a specified interval. Previous-month or previous-year comparisons.
SAMEPERIODLASTYEAR() Returns the corresponding dates from the previous year. Year-over-year analysis.
TOTALYTD() Evaluates an expression year to date. Running yearly totals.

4. Create a Base Sales Measure

Most time intelligence calculations begin with a base measure. Suppose your fact table is named Sales and contains a numeric column named SalesAmount.

Total Sales =
SUM(Sales[SalesAmount])

Create this measure first. The examples below use it to calculate sales for different time periods.

5. Year-to-Date (YTD)

Year-to-date calculates a measure from the beginning of the current year through the date in the current filter context.

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

For example, if the current context ends on 15 March 2025, the measure calculates sales from 1 January through 15 March 2025, subject to the model's date relationships and filters.

6. Month-to-Date (MTD) and Quarter-to-Date (QTD)

Month-to-date measure:

Sales MTD =
CALCULATE(
    [Total Sales],
    DATESMTD(DateTable[Date])
)

Quarter-to-date measure:

Sales QTD =
CALCULATE(
    [Total Sales],
    DATESQTD(DateTable[Date])
)
Period Accumulation MTD Start of month → current date QTD Start of quarter → current date YTD Year Illustrative ranges only; actual results depend on the selected dates.

7. Compare Sales with the Previous Year

Use SAMEPERIODLASTYEAR() to evaluate the sales measure for the corresponding period in the previous year.

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

Calculate the difference between the current period and previous year:

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

Calculate the percentage change:

Sales YoY Percentage =
DIVIDE(
    [Sales YoY Difference],
    [Sales Previous Year]
)
Formatting Tip: Select the percentage measure and set its format to Percentage in Power BI. The DIVIDE() function handles a zero or blank denominator more safely than using the division operator directly.

8. Use DATEADD for Period Comparisons

DATEADD() shifts the current date context by a specified number of intervals. It can be used for comparisons with the previous month, quarter, or year.

Sales Previous Month =
CALCULATE(
    [Total Sales],
    DATEADD(DateTable[Date], -1, MONTH)
)
Sales Previous Quarter =
CALCULATE(
    [Total Sales],
    DATEADD(DateTable[Date], -1, QUARTER)
)
Sales Previous Year Using DATEADD =
CALCULATE(
    [Total Sales],
    DATEADD(DateTable[Date], -1, YEAR)
)
Period Comparison Previous Month February DATEADD Current Month March The measure shifts the date context to evaluate another period.

9. Practical Example: Monthly Sales Analysis

Assume the Sales table contains the following sample records. These values are illustrative and are used to understand period comparisons.

Order Date Sales Amount
10 January 2024 ₹20,000
15 February 2024 ₹25,000
10 January 2025 ₹30,000
15 February 2025 ₹35,000

Create a table visual containing the month from DateTable, along with [Total Sales], [Sales Previous Year], and [Sales YoY Percentage]. Check that the date table is related to the Sales table before interpreting the results.

10. Common Errors and Troubleshooting

Problem Possible Cause Recommended Check
Previous-year values are blank. Missing dates, incorrect relationship, or no matching prior-year dates. Check the date table range, relationship, and selected period.
YTD totals appear incorrect. The visual uses an unrelated date field or the model has an incorrect relationship. Use the date table field and verify the model relationships.
Months appear in alphabetical order. Month names are sorted alphabetically. Sort Month Name by Month Number.
Time intelligence gives unexpected results. Duplicate dates, gaps, inactive relationships, or unexpected filter context. Validate the calendar and inspect the visual filters.

11. Student Practice Tasks

Task 1: Create a DateTable covering the dates in your sample Sales dataset.

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

Task 3: Create measures for Total Sales, Sales YTD, Sales MTD, and Sales QTD.

Task 4: Create Sales Previous Year and Sales YoY Percentage measures.

Task 5: Build a table visual that compares monthly sales across two years.

Task 6: Confirm that months are sorted chronologically and that date relationships are active.

12. Knowledge Check

Question 1: What is the purpose of a date table?

Answer: It provides a consistent calendar for filtering, grouping, and comparing dates.

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

Answer: TOTALYTD() or CALCULATE() combined with DATESYTD().

Question 3: Which function returns the corresponding period from the previous year?

Answer: SAMEPERIODLASTYEAR().

Question 4: Why should Month Name be sorted by Month Number?

Answer: To display months in calendar order rather than alphabetical order.

Question 5: What should you verify if a previous-year measure returns blank?

Answer: Check the date table, date range, relationships, and selected date context.

Summary

  • Date intelligence supports analysis across months, quarters, and years.
  • A properly configured date table is essential for dependable time-based calculations.
  • TOTALYTD(), DATESMTD(), and DATESQTD() support period-to-date analysis.
  • SAMEPERIODLASTYEAR() and DATEADD() help compare results across periods.
  • Year-over-year differences and percentages help explain changes in business performance.
  • Always validate date coverage, data types, relationships, and sorting before trusting the results.