Use the Cumulative Total pattern to change the context from the particular selected date to all dates up to and including the current date.
Cumulative Total =
// DAX PATTERN NAME: Cumulative Total
// NOTES: Use CALCULATE to change the context from the particular selected date to all dates up to and including the current date`
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
[Total Sales], // replace [Total Sales] with the name of the [Total] measure
FILTER(
ALLSELECTED( Dates ), // replace [Dates] with the name of the [Dates] table
Dates[Date] <= MAX( Dates[Date] ) // replace Dates[Date] with the name of the main [Date] field in the [Dates] table
)
)
To extend this pattern to, for instance, Cumulative Total Last Year, copy and paste the Cumulative Total measure into a new measure and simply replace the [Total Sales] measure with the [Sales LY] measure. e.g.,
Cumulative Total LY =
// NOTES: Use CALCULATE to change the context from the particular selected date to all dates up to and including the current date`
// TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once
// TIP: Use CRTL + mouse scroll wheel to zoom the text size
CALCULATE(
[Sales LY], // replace [Sales LY] with the name of the [Total Sales LY] measure
FILTER(
ALLSELECTED( Dates ), // replace [Dates] with the name of the [Dates] table
Dates[Date] <= MAX( Dates[Date] ) // replace Dates[Date] with the name of the main [Date] field in the [Dates] table
)
)
Tips:
To replace all occurrences of a text string = select one occurrence, use CRTL + SHIFT + L, type the replacement text
To zoom the text size = use CRTL + mouse scroll wheel
