Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NR5046
Regular Visitor

Cumulative Sum with Restricted Dates

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.

2 REPLIES 2
Anonymous
Not applicable

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
Community Champion
Community Champion

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
    )

 

 

 

1.jpg

 

Regards,
Harsh Nathani

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.