DAXDAX Trainer

DAX Concept

Mastering Power BI Time Intelligence: Automatic Date Tables and DAX Functions

Learn to leverage Power BI's automatic date tables to create robust time intelligence measures using DAX functions like TOTALMTD, SAMEPERIODLASTYEAR, DATEADD, and ISINSCOPE for calculating running totals and period-over-period comparisons.

IntermediateTOTALMTDIFCALCULATESAMEPERIODLASTYEARDATEADDISINSCOPE

Concept Breakdown

Power BI automatically generates hidden date tables for each date column in your model, facilitating calendar-based time intelligence. DAX time intelligence functions simplify complex date calculations, enabling comparisons across various time periods (e.g., month-to-date, year-over-year). Additionally, functions like ISINSCOPE allow for dynamic display logic based on the granularity of dates present in a visual.

What You Will Learn

  1. Understand when to use TOTALMTD, IF, CALCULATE, SAMEPERIODLASTYEAR, DATEADD, ISINSCOPE 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 'Music Tours - Basic Time Intelligence.pbix' file.

  2. Open the 'Music Tours - Basic Time Intelligence.pbix' file in Power BI Desktop.

  3. Navigate to Page 1, which contains a matrix of show dates, tickets sold, and revenue.

  4. Create a new measure named 'Tickets Sold MTD' to calculate a monthly running total of tickets sold using the TOTALMTD function. Ensure you reference the 'Date' column of the automatic calendar table related to the 'Show date' field (e.g., `Dates[Date]`).

  5. Add the 'Tickets Sold MTD' measure to the matrix.

  6. Modify the 'Tickets Sold MTD' measure to include an IF function. This function should check if the sum of tickets sold for a given date is blank, and if so, hide that date from the matrix.

  7. Create another new measure named 'Revenue MTD' for a monthly running total of show revenue. Apply the same logic as 'Tickets Sold MTD' to hide dates with no ticket sales. (Optionally, format this measure as currency).

  8. Navigate to Page 2, which displays a matrix of total track length released as singles, grouped by year and month.

  9. Create a measure named 'Single Length YOY Change' to compare the sum of single length with the same period in the previous year, using a combination of CALCULATE and SAMEPERIODLASTYEAR functions.

  10. Add the 'Single Length YOY Change' measure to the matrix. (Optionally, apply conditional formatting to highlight positive and negative numbers).

  11. Create a measure named 'Single Length MoM Change' to compare the sum of single length with the same value from one month ago, using CALCULATE and DATEADD functions.

  12. Add the 'Single Length MoM Change' measure to the matrix. (Optionally, apply conditional formatting).

  13. Modify the 'Single Length MoM Change' measure to only show a result when the 'Month' column of the 'Single release date' field's automatic calendar table (e.g., `Dates[Month]`) is in scope, using the ISINSCOPE function.

  14. Save and close the report.

Starter DAX

Tickets Sold MTD Initial = 
TOTALMTD ( SUM ( 'FactSales'[Tickets Sold] ), Dates[Date] )

Expected Outcome

On Page 1, the matrix will display monthly running totals for 'Tickets Sold' and 'Revenue', with dates that have no ticket sales hidden. On Page 2, the matrix will show year-over-year and month-over-month comparisons for 'Single Length'. The 'Single Length MoM Change' measure will only display values when the 'Month' column is present in the visual's scope, ensuring context-appropriate results.

Checking your sign-in status...