DAXDAX Trainer

DAX Concept

Calculating Cumulative Year-to-Date Purchases, Previous Month's Purchases, and Their Ratio using Date Intelligence Functions

Learn to apply DAX date intelligence functions like TOTALYTD, DATESYTD, and PARALLELPERIOD in conjunction with CALCULATE to perform time-based calculations and derive insightful ratios.

IntermediateCALCULATETOTALYTDDATESYTDPARALLELPERIODDIVIDE

Concept Breakdown

This exercise focuses on essential DAX date intelligence functions. `TOTALYTD` and `DATESYTD` are used for year-to-date aggregations, demonstrating how to accumulate values over a fiscal year. `PARALLELPERIOD` is crucial for shifting the date context to a preceding or succeeding period, such as the previous month, quarter, or year. `CALCULATE` is used to modify the filter context in which these calculations are performed, allowing for dynamic time-based analysis. The `DIVIDE` function is used for safe division to handle potential division by zero scenarios.

What You Will Learn

  1. Understand when to use CALCULATE, TOTALYTD, DATESYTD, PARALLELPERIOD, DIVIDE in a Power BI model.

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

  3. Complete a intermediate-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 and unzip the provided exercise file containing the Power BI data source.

  2. Open Power BI Desktop and load both tables from the unzipped workbook into your model. (Assume 'Sales' for purchase data and 'Calendar' for dates).

  3. In the Model view, create a relationship between the date column in your 'Sales' table and the 'Date' column in your 'Calendar' table (one-to-many, single direction from Calendar to Sales).

  4. Mark your 'Calendar' table as a date table in Power BI Desktop (Table tools > Mark as date table).

  5. Create a base measure named `Total Purchases` to count the total number of purchase rows from your 'Sales' table: `Total Purchases = COUNTROWS(Sales)`.

  6. Create a new measure named `YearToDate Purchases` that calculates the cumulative year-to-date total of `Total Purchases` using the `TOTALYTD` function.

  7. Create a new measure named `PreviousMonth Purchases` that calculates the `Total Purchases` for the previous month using `CALCULATE` and `PARALLELPERIOD`.

  8. Create a final measure named `Cumulative Ratio` that divides `YearToDate Purchases` by `PreviousMonth Purchases`, ensuring to handle division by zero with `DIVIDE`.

  9. Create a table visual on a new report page. Add the 'Date' column from your 'Calendar' table, and your three new measures: `YearToDate Purchases`, `PreviousMonth Purchases`, and `Cumulative Ratio`.

  10. Apply a page or visual level filter to show data only for January 2019 to observe the specific results mentioned in the exercise.

  11. Save your Power BI file.

Starter DAX

YearToDate Purchases =

Expected Outcome

A table visual filtered for January 2019, displaying a daily breakdown of 'YearToDate Purchases', 'PreviousMonth Purchases', and their 'Cumulative Ratio'. The values for 'PreviousMonth Purchases' will reflect December 2018 figures for each day in January 2019.

Checking your sign-in status...