Cumulative Total

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
4 Likes

Just adding some keywords to make this easier to locate via forum search:
DAX pattern, aggregate, cumulative, total

Related Content:

Below are a few examples of issues related to the Cumulative Total DAX pattern from the eDNA resources.

Enterprise DNA Forum (Search):



Enterprise DNA Forum (EDNA):



Enterprise DNA TV (YouTube):

Cumulative Totals Deep Dive - Power BI & DAX Formula Review

Show MTD, QTD & YTD Calculations To Current Date in Power BI w/DAX

Dynamically Compare Monthly Information Cumulatively in Power BI w/ DAX

Cumulative Totals Only Up To Specific Dates - DAX Tutorial For Power BI