DAXDAX Trainer

DAX Concept

Mastering Advanced Time Intelligence Measures in DAX

Create three complex DAX time intelligence measures: a quarter-over-quarter percentage, a year-to-date percentage against the previous full year, and a three-month moving average of monthly totals.

AdvancedCALCULATEDATEADDDATESYTDSAMEPERIODLASTYEARDATESINPERIODDIVIDESUMAVERAGEXSUMMARIZEADDCOLUMNS

Concept Breakdown

This exercise delves into advanced time intelligence by leveraging the CALCULATE function's ability to modify filter context. It combines standard time intelligence functions like DATEADD, DATESYTD, and SAMEPERIODLASTYEAR for direct period comparisons with more intricate patterns using DATESINPERIOD, SUMMARIZE, ADDCOLUMNS, and AVERAGEX to compute rolling averages of aggregated values (e.g., monthly totals) rather than daily values, requiring a deeper understanding of context transition and table manipulation.

What You Will Learn

  1. Understand when to use CALCULATE, DATEADD, DATESYTD, SAMEPERIODLASTYEAR, DATESINPERIOD, DIVIDE, SUM, AVERAGEX, SUMMARIZE, ADDCOLUMNS in a Power BI model.

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

  3. Complete a advanced-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 open the provided Power BI (.pbix) file.

  2. Create a base measure named `Total Quantity Sold` that sums the 'Quantity' column from your sales table.

  3. Create the first DAX measure named `% of Previous Quarter` to calculate the current quarter's total quantity sold as a percentage of the previous quarter's total.

  4. Create the second DAX measure named `YTD % of All Previous Year` to calculate the cumulative Year-To-Date total quantity sold as a percentage of the total quantity sold for the entire previous year.

  5. Create the third DAX measure named `3-Month Moving Average` to compute the average of the total quantity sold for the preceding three months (averaging the monthly totals).

  6. Add these three new measures alongside 'Year', 'Quarter', 'Month', and `Total Quantity Sold` to a table visual to verify your results against the exercise's expected output screenshot.

  7. Save your Power BI file as 'Desperate measures'.

Starter DAX

Total Quantity Sold = SUM('Sales'[Quantity])

Expected Outcome

A table visual displaying 'Year', 'Quarter', 'Month', 'Total Quantity Sold', and the three new measures, with '% of Previous Quarter' and 'YTD % of All Previous Year' showing accurate percentages and '3-Month Moving Average' showing the correct rolling average of monthly sales totals.

Checking your sign-in status...