Latest Enterprise DNA Initiatives


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