Concept Breakdown
The TOTALYTD function calculates a year-to-date expression, allowing for an optional 'year_end_date' parameter. This parameter is crucial when dealing with fiscal years that do not align with the standard calendar year (January 1st to December 31st). By specifying the fiscal year end (e.g., '3/31' for March 31st), TOTALYTD correctly resets the year-to-date calculation at the end of each fiscal period. This relies on a properly marked date table in your model.
What You Will Learn
Understand when to use TOTALYTD in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
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.
Download and unzip the provided exercise files, then open the 'Music Tours - Time Intelligence and Custom Calendars.pbix' Power BI Desktop file.
Import the 'Track calendar' worksheet from the 'Track calendar.xlsx' Excel file into your Power BI model.
In Power BI Desktop, navigate to the Model view. Select the newly imported 'Track calendar' table and mark it as a Date table, designating the 'Date' column as the Date column.
Disable automatic summarization for any numeric columns (e.g., Year, Month Number, Weekday Number) in the 'Track calendar' table.
Sort the 'Month' column by 'Month Number' and the 'Weekday' column by 'Weekday Number' to ensure correct chronological order in visuals.
Hide irrelevant columns (e.g., 'Month Number', 'Weekday Number') from the Report view.
Create a relationship between the 'Track' table's date column and the 'Track calendar'[Date] column.
Create a matrix visual. Add appropriate date hierarchy fields from your 'Track calendar' table to the rows or columns, and add existing measures from the 'Music Measures' table to the values.
Apply a visual-level filter to the matrix so that it only displays dates from April 1, 1980, onwards.
Create a new DAX measure called 'Tracks YTD' that calculates the year-to-date count of tracks, respecting the fiscal year end of March 31st (using the 'Track calendar' Date column). Add this measure to your matrix visual.
Similarly, create year-to-date measures for the other two existing measures in the matrix, applying the same fiscal year end logic.
Starter DAX
Total Tracks = COUNT('Track'[Track Name])Expected Outcome
A matrix visual displaying various measures, including 'Total Tracks' and 'Tracks YTD', where the 'Tracks YTD' measure correctly aggregates values from the start of the fiscal year (April 1st) up to the current date in the visual, resetting annually on March 31st. The visual should be filtered to show data from April 1, 1980, onwards.