DAXDAX Trainer

DAX Concept

Time Intelligence: Comparing Porpoise Sightings with Previous Periods

Learn to use fundamental DAX time intelligence functions like PREVIOUSMONTH and DATEADD to compare current period data with previous periods, enhancing temporal analysis in Power BI.

BeginnerPREVIOUSMONTHDATEADD

Concept Breakdown

DAX Time Intelligence functions allow for calculations across different time periods (e.g., year-to-date, previous month, same period last year) when a properly configured Date table is present. PREVIOUSMONTH is a specialized time intelligence function that shifts the context back by exactly one month. DATEADD is a more general-purpose time intelligence function that allows you to shift the context by a specified number of intervals (e.g., days, months, years) forward or backward.

What You Will Learn

  1. Understand when to use PREVIOUSMONTH, DATEADD in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a beginner-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download the provided Power BI file and load it into Power BI Desktop.

  2. Load the 'Calendar' Excel file from the same folder into your Power BI model.

  3. Create a relationship between the 'Calendar'[Date] column and the 'Sightings'[Date] column.

  4. Mark the 'Calendar' table as a Date table (Modeling tab > Mark as Date table).

  5. Sort the 'Calendar'[MonthName] column by 'Calendar'[MonthNumber] (Column tools > Sort by column).

  6. Create a new measure called `Harbour Porpoise Sightings` to sum the `Count` column specifically for 'Harbour Porpoise' species, as shown in the `starter_code`.

  7. Create a new Matrix visual. Add 'Year' and 'MonthName' from the 'Calendar' table to the Rows field. Add the `Harbour Porpoise Sightings` measure to the Values field. Ensure 'Year' is not summed (right-click on 'Year' in Values and select 'Don't summarize'). Expand the matrix to show months.

  8. Create a new measure called `Previous month` using the `PREVIOUSMONTH` function to show sightings from the month before, as shown in the `dax_code`.

  9. Create another new measure called `Two months ago` using the `DATEADD` function to show sightings from two months prior, as shown in the `dax_code`.

  10. Add both new measures (`Previous month` and `Two months ago`) to the Values field of your Matrix visual.

Starter DAX

Harbour Porpoise Sightings = 
CALCULATE(
    SUM('Sightings'[Count]),
    'Sightings'[Species] = "Harbour Porpoise"
)

Expected Outcome

A matrix displaying Harbour Porpoise sightings for each month, alongside their respective previous month's sightings and sightings from two months prior, allowing for direct comparison across time periods.

Checking your sign-in status...