Cumulative Sum with Restricted Dates

[NR5046]

Hello!

I am having an issue with my cumulative sum formula. I just created a universal date table to connect and filter all of my other tables. The dates range 200 years. I now am trying to create a cumulative sum formula in my cost table, but am running into performance issues. Charts are taking very long to work when filters are put in place (I'm assuming that is due to size of my date table). Is there anyway to restrict the date table to enhance performance? I want to try and restrict the date table between a min and max date derived from my cost table. The cost table I am using for the cumulative sum only has 2 years worth of data. Here is an example of what I have tried:

VAR MinDate = min('CostTable'[Date])

VAR MaxDate = max('CostTable'[Date])

CALCULATE([CumulativeSum],

FILTER(

ALL('DateTable'[Date]),

AND('DateTable'[Date]>=[MinDate], 'DateTable'[Date].[Date] <= MaxDate))))

I have tried using allselected instead of all in the previous as well. I cannot share my data set, but if you can help it would be greatly appreciated. Thanks!

Edit: This is one of several formulas I have written so I have used all and allselected where I thought was needed.

[Anonymous]

To use ALLSELECTED you have to know how it works. Throwing functions (semi-randomly) at a formula without a deep understanding of how a function works is a sure way to fail.

Try this code:

[Cumul Sum] =
var __lastVisibleDate = MAX( CostTable[Date] )
return
	CALCULATE(
		SUM( CostTable[ColumnToSumOver] ),
		CostTable[Date] <= __lastVisibleDate
		ALL( DateTable )
	)

// Here's a different version
// that will give you the correct VISUAL
// cumulative but only if the dates
// visible are consecutive.
[Cumul Sum] =
var __lastVisibleDate = MAX( CostTable[Date] )
var __firstVisibleDate =
	CALCULATE(
		MIN( CostTable[Date] ),
		ALLSELECTED( DateTable )
	)
return
	CALCULATE(
		SUM( CostTable[ColumnToSumOver] ),
		CostTable[Date] <= __lastVisibleDate,
		__firstVisibleDate <= CostTable[Date],
		ALL( DateTable )
	)

// And this version will give you
// the correct visual cumulative 
// even for non-contiguous dates.
[Cumul Sum] =
var __lastVisibleDate = MAX( DateTable[Date] )
return
CALCULATE(
	SUM( CostTable[ColumnToSumOver] ),
	KEEPFILTERS(
		DateTable[Date] <= __lastVisibleDate
	),
	ALLSELECTED( DateTable )
)

[harshnathani]

Hi @NR5046 ,

Use this measure

Running Total =
VAR MaxDate =
    MAX ( 'Dates'[Date] ) -- Saves the last visible date in the slicer
VAR MinDate =
    CALCULATE (
        MIN ( Dates[Date] ),
        ALLSELECTED ( Dates[Date] )
    )
RETURN
    CALCULATE (
        [Total Sales],
        'Dates'[Date] <= MaxDate
            && Dates[Date] >= MinDate
    )

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!