Concept Breakdown
Quarter-to-Date (QTD) calculations aggregate values from the beginning of the current quarter up to the current date within the filter context. The `TOTALQTD` function provides a concise way to achieve this. Alternatively, `CALCULATE` combined with `DATESQTD` can be used for more flexible QTD calculations. To retrieve values from a previous period, `DATEADD` is used within `CALCULATE`. `DATEADD` shifts the date context by a specified number of intervals (e.g., -1 quarter), allowing for direct comparisons with historical periods.
What You Will Learn
Understand when to use CALCULATE, TOTALQTD, DATESQTD, DATEADD, SUM in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
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.
Download and unzip the provided data file (implied from the exercise text).
Create a new Power BI file and load the necessary tables from the downloaded worksheets.
Ensure your 'Calendar' table is correctly set up and marked as a date table.
Create a relationship between the 'Purchase Date' column in the 'Purchase' table and the 'Date' column in the 'Calendar' table.
Create a Matrix visual. Add 'Quarter' and 'Month' from the 'Calendar' table to the Rows section.
Apply a visual-level filter to the Matrix to show only figures for 'Calendar Year = 2019'.
Create a base measure called `Total Sales` using `SUM( 'Purchase'[Quantity] )`.
Create a new DAX measure called `QTD Sales` to display quarter-to-date sales figures, using either the `TOTALQTD` function or `CALCULATE` with `DATESQTD`.
Create another new DAX measure called `Previous Quarter Sales` to show sales from the prior quarter, utilizing `CALCULATE` and `DATEADD`.
Add `Total Sales`, `QTD Sales`, and `Previous Quarter Sales` to the Values section of your Matrix visual.
Starter DAX
Total Sales = SUM( 'Purchase'[Quantity] )Expected Outcome
A Matrix visual displaying `Total Sales`, `QTD Sales`, and `Previous Quarter Sales` for each month and quarter of 2019. `QTD Sales` will accumulate within each quarter, and `Previous Quarter Sales` will show the `Total Sales` from the immediately preceding quarter.